PostgreSQL
Links
Database
|
-- list databases
\l
-- connect to a database
\c myDatabaseName
-- drop a database
drop [if exists] myDatabaseName;
|
|
# create a database
createdb myDatabaseName
# connect to a database
psql -d myDatabaseName
# drop a database
dropdb myDatabaseName
|
Create Table
|
|
-- show summary information about all tables in the current database
\dt
CREATE TABLE [table] (
[column] smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
[column] text NOT NULL UNIQUE CHECK ([column] <> '')
);
|
 |
PostgreSQL automatically creates indexes on primary keys and unique constraints. |
Foreign key
|
|
CREATE TABLE group (
id smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
CREATE TABLE user (
group_id smallint references group(id),
);
|
Generated column
|
PostgreSQL currently implements only stored generated columns |
|
|
CREATE TABLE people (
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
|
Alter Table
|
|
ALTER TABLE [table]
DROP COLUMN [column] RESTRICT;
ALTER TABLE [table]
ADD COLUMN [column] [type] DEFAULT 'xxx';
ALTER TABLE [table]
ALTER COLUMN [column] TYPE [type];
|
Users and permissions
|
|
-- list all users and their permission levels
\du
CREATE USER [user] WITH PASSWORD 'xxxxxx';
|
|
|
createuser --interactive dropuser <user> |
Data types
| Name | Alias | .NET | Description |
|---|---|---|---|
| integer | int, int4 | int | signed four-byte integer |
| smallint | int2 | short | signed two-byte integer (-32,768 to 32,767) |
| bigint | int8 | long | signed eight-byte integer |
| numeric [ (p, s) ] | decimal [ (p, s) ] | decimal | exact numeric of selectable precision |
| money | currency amount on 8 bytes (-92233720368547758.08 to +92233720368547758.07) | ||
| text | string | variable-length character string | |
| character varying(n) | varchar(n) | string | variable-length character string |
| character(n) | char(n) | char[n] | fixed-length character string |
| boolean | bool | bool | logical Boolean (true/false) |
| timestamp | Datetime | date and time (no time zone) | |
| timestamp with time zone | timestamptz | Datetime | date and time, including time zone |
| date | Date | calendar date (year, month, day) | |
| time | Time | time of day (no time zone) | |
| time with time zone | timetz | Time | time of day, including time zone |
|
|
Service
|
|
sc-start postgresql.service |
Installation
|
|
# will install postgresql-libs, postgresql and create a system user called postgres
sudo pacman -S postgresql
|
Configuration
|
|
# create the cluster directory if needed
sudo mkdir /var/lib/postgres
sudo chown postgres:postgres /var/lib/postgres
# switch to the PostgreSQL user
sudo -iu postgres
# initialize the database cluster (postgres user)
initdb -D /var/lib/postgres/data
# $LANG (en_US.UTF-8) is used to deduce the locale and the encoding
# or can be defined manually:
initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
# --data-checksums enable data checksumming
# start the service
sc-start postgresql.service
# login (postgres user)
psql
# create a user (postgres user)
# If the new user has the same name as your Linux user, it allows you to access the PostgreSQL database shell without having to specify a user to login.
createuser --interactive
# create a database (postgres user)
createdb dbtest
# login (current user)
psql -d dbtest
|
Access rights
| /var/lib/postgres/data/pg_hba.conf |
# all local users can access to any database including superuser database
local all all trust
# only local postgres user can login
local all postgres peer
|
Installation on Windows
|
winget install PostgreSQL.PostgreSQL |
|
default user and password: postgres |
|
|
# connection to the database
C:\Program Files\PostgreSQL\16\bin\psql.exe -U [user] -d [db]
|