PostgreSQL

De Banane Atomic
Aller à la navigationAller à la recherche

Links

Database

Pgsql.svg
-- list databases
\l

-- connect to a database
\c myDatabaseName

-- drop a database
drop [if exists] myDatabaseName;
Bash.svg
# create a database
createdb myDatabaseName

# connect to a database
psql -d myDatabaseName

# drop a database
dropdb myDatabaseName

Create Table

Pgsql.svg
-- 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

Pgsql.svg
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
Pgsql.svg
CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Alter Table

Pgsql.svg
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

Pgsql.svg
-- list all users and their permission levels
\du

CREATE USER [user] WITH PASSWORD 'xxxxxx';
Bash.svg
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
  • no one-byte integer
  • no unsigned integer

Service

Bash.svg
sc-start postgresql.service

Installation

Bash.svg
# will install postgresql-libs, postgresql and create a system user called postgres
sudo pacman -S postgresql

Configuration

Bash.svg
# 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

Ps.svg
winget install PostgreSQL.PostgreSQL
default user and password: postgres
Ps.svg
# connection to the database
C:\Program Files\PostgreSQL\16\bin\psql.exe -U [user] -d [db]