Links
Database
|
-- list databases
\l
-- connect to a database
\c <db>
create database <db>;
-- drop a database
drop [if exists] <db>;
|
|
# create a database
createdb <db>
# connect to a database
psql -d <db>
# drop a database
dropdb <db>
# get the server version
SELECT version();
|
|
|
-- 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. |
|
|
CREATE TABLE group (
id smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
CREATE TABLE user (
group_id smallint references group(id),
);
|
|
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 [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';
-- switch user on the current database
\c - [user]
-- connect to a database as a user
\c <db> [user]
-- grant access to the database
grant connect on database <db> to <user>;
-- ensure your are connected to the database first
-- grant access to the schema public (of the current database)
grant usage on schema public to <user>;
-- grant read/write access to all the tables
grant select, insert, update, delete on all tables in schema public to <user>;
-- grant access to the future tables
alter default privileges in schema public
grant select, insert, update, delete on tables to <user>;
|
|
|
createuser --interactive
dropuser <user>
|
| 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
|
|
- no one-byte integer
- no unsigned integer
|
Connection string
postgresql://myuser:mysecret@localhost:5432/mydatabase
Host=localhost;Database=MyDb;Username=root;Password=pwd;Include Error Detail=true
Service
|
|
sc-start postgresql.service
|
Backup and restore
|
|
# backup
sudo -u postgres pg_dump -d [database_name] -F t -f [/path/to/backup_file.tar]
# restore
sudo -u postgres pg_restore -d [database_name] -v [/path/to/backup_file.tar]
|
CSV
|
|
\copy tablename (column1, column2, ...) FROM '/path/to/file.csv' DELIMITER ',' CSV;
|
|
|
# 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)
sudo -u postgres 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, default port server: 5432 |
|
|
# connection to the database
C:\Program Files\PostgreSQL\16\bin\psql.exe -U [user] -d [db]
|