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

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] <> '') );

• CREATE TABLE

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 );

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)
date		Date	calendar date (year, month, day)
time [ (p) ] [ without time zone ]		Time	time of day (no time zone)
time [ (p) ] 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]