PostgreSQL

Links

• PostgreSQL on Archlinux
• pgAdmin

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>

• CREATE 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

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]