PostgreSQL

Table of Contents General Install Standalone Linux Latest version LTS Container (Docker) Windows Linux Disable implicit COMMIT DB Session Command prompt Misc User Reset admin password DDL Case sensitive DML Case sensitive Types Function SQL Functions string Transaction isolation level Syntax Logging Docker setup logs container docker-compose access logs Session time Delete data Some rows A whole table A single row and its dependencies Lint

General

Note:

• pg stands for PostgreSQL daemon
• DB is accessible through URL-like adresses, postgres://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DB_NAME>, eg postgres://foo:bar@localhost:5432/videotapes
• pg_cli stands for PostgreSQL command-line interface (psql)
• ALWAYS end pg_cli instructions with semicolon ;
• pg_cli connect this way psql postgres://postgres@localhost:5432/my_database
• default port is 5432, but can be altered in /etc/postgresql/<VERSION>/main/postgresql.conf (ubuntu)

Links:

• cheatsheet
• metamodel
• colorize explain plan

List:

• check ps is running
  • pg_lsclusters
  • systemctl list-unit-files | grep enabled | grep postgres
  • pgrep -u postgres -fa -- -D
• stop postgresql service:
  • sudo service postgresql stop
  • you may need to use version sudo systemctl stop postgresql@12-main
• start server: pg_ctl start -l logfile or here

Install

Standalone

Linux

Latest version

Start here

LTS

Steps (from there):

• install it using package manager (with aptitude: sudo apt-get install postgresql postgresql-contrib libpq-dev
• check startup log: tail /var/log/postgresql/postgresql-*.log
• check postgreSQL is started by opening prompt sudo -u postgres psql , leave by \q
• create an linux user account sudo adduser <OS_USER_NAME>
• create a role sudo -u postgres createuser --interactive , supplying the newly created linux user account ( <POSTGRESQL_ROLE_NAME> = <OS_USER_NAME> )

Additionally, enable access from outside:

• locate your authentication file sudo find /etc/postgresql -name "pg_hba.conf"
• sudo vi <PATH_TO_PG-HBA>/pg_hba.conf
• locate block

# Database administrative login by Unix domain socket
local   all             postgres                                peer

• change peer to md5
• reload pg service sudo /etc/init.d/postgresql reload

Container (Docker)

Links:

• Build your own image

Windows

Overview:

• create a user named postgres, add it to Administrator group;
• open a windows as postgres user: runas /user:postgres cmd.exe .

Steps:

• create data container : docker create -v /var/lib/postgresql/data --name PostgreSQLData alpine
• create executable container : docker run -p 5432:5432 --name PostgreSQL -e POSTGRES_PASSWORD=yourPassword -d --volumes-from PostgreSQLData postgres

More on this

Linux

Steps:

• create data container : docker create -v /var/lib/postgresql/data --name PostgreSQLData alpine
• create executable container : docker run -p 5432:5432 --name PostgreSQL -e POSTGRES_PASSWORD=yourPassword -d --volumes-from PostgreSQLData postgres
• connect to database: docker exec -it --user postgres PostgreSQL psql
• create database: create database sandbox;
• switch to database \connect sandbox;

Or, with a one-liner

mkdir -p PATH_TO_DB_DATA_STORAGE
docker run 
  --name    CONTAINER_NAME
  --publish LOCAL_PORT:5432 
  --env     POSTGRES_DB=DATABASE_NAME
  --env     POSTGRES_USER=USER_NAME
  --env     POSTGRES_PASSWORD=USER_PASSWORD
  --volume  PATH_TO_DB_DATA_STORAGE:/var/lib/postgresql/data 
  --detach
  postgres

Load a sample database

• get backup
• extract it, whould get a .tar file
• upload backup inside container docker cp ./dvdrental.tar PostgreSQL:/tmp
• create database dvdrental
• import backup pg_restore -U postgres -d dvdrental /tmp/dvdrental.tar
• connect: docker exec -it --user postgres PostgreSQL psql -d dvdrental
• list tables \dt
• check content: SELECT COUNT(*) FROM city;

Disable implicit COMMIT

DB

Steps:

• get pg location pg_config --sysconfdir
• create a global initialization file sudo vi <PG_LOCATION>/psqlrc
• paste \set AUTOCOMMIT off
• restart OS and check

Session

Steps:

• open pg CLI
• type \set AUTOCOMMIT OFF
• commit COMMIT;
• check \echo :AUTOCOMMIT

Command prompt

Get a nice command prompt (originally here)

-- Command prompt config
-- on main prompt, display [local/domain] user@database
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
-- on a multi-line prompt, displays "more"
\set PROMPT2 '[more] %R > '

Misc

-- By default, NULL displays as an empty space. 
-- Is it actually an empty string, or is it null? 
-- This makes that distinction visible displaying [NULL] instead
\pset null '[NULL]'
-- Use table format (with headers across the top) by default, but switch to
-- expanded table format when there's a lot of data, which makes it much
-- easier to read.
\x auto
-- Verbose error reports.
\set VERBOSITY verbose
-- Use a separate history file per-database.
\set HISTFILE ~/.psql_history- :DBNAME
-- If a command is run more than once in a row, only store it once in the
-- history.
\set HISTCONTROL ignoredups
-- Autocomplete keywords (like SELECT) in upper-case, even if you started
-- typing them in lower case.
\set COMP_KEYWORD_CASE upper

User

Reset admin password

Lost admin (postgres) password:

• add to pg_hba.conf : local all all trust
• restart : sudo service postgresql restart
• connect : sudo -u postgresql psql
• restart : ALTER USER postgres with password '<PASSWORD>';
• comment in pg_hba.conf : local all all trust
• restart : sudo service postgresql restart
• connect : sudo -u postgresql psql

DDL

Case sensitive

Reference

2 basic rules:

• if name is enclosed by double quotes (not simple), case is preserved: "testData" => "testData"
• if not, its case if transformed to lower-case : testData => testdata

So, when using double quotes to define the name :

• case is preserved;
• name is stored including double quotes, so can only be referenced using double-quotes.

CREATE TABLE "Test" (id INTEGER);
INSERT INTO test VALUES (1);
relation "test" does not exist

CREATE TABLE "test" (id INTEGER);
INSERT INTO test VALUES (1);
INSERT 0 1

What can be an annoying behavior on a single word can be much worse when using more than one word.

CREATE TABLE IngredientRecipeByName (idRecipe INTEGER);
INSERT INTO IngredientRecipeByName (idRecipe) VALUES (1);

Although you think everything is right, when you browse table, it appears as ingredientrecipebyname. If you want to keep the CamelCase, you'll have to

CREATE TABLE "IngredientRecipeByName" ( "idRecipe" INTEGER );
INSERT INTO "IngredientRecipeByName" ( "idRecipe" ) VALUES ( 1 );

If you want to dispense of double-quotes entirely, to save you loads of double-quotes, but still get readable names, switch to snake_case case-insensitive pattern. Kebab-spinal-case isn't supported BTW

CREATE TABLE recipe-ingredient (recipe-id INTEGER, ingredient-id);
syntax error at or near "-"

CREATE TABLE recipe_ingredient (recipe_id INTEGER, ingredient_id INTEGER);
INSERT INTO recipe_ingredient (recipe_id, ingredient_id) VALUES (1, 3);
CREATE TABLE recipe_ingredient (recipe_id INTEGER, ingredient_id INTEGER);

Another special case: you need double-quote around column name, even when not case-sensitive in a SET See here

DML

Reference

Case sensitive

Types

Basic:

• test: TEXT
• number:
  • integer: INTEGER
  • with decimal part: NUMBER

Function

API

List:

• current user:
  • current_user
  • SELECT * FROM user;, misleading if you create a table named user yourself)
• current database: current_database()
• current user: current_database()

SQL Functions

Some of them are:

• SQL compliant
• specific

string

Full API

A naïve parsing of email

-- p_new_email = username@domain-name.com
arobase_position_in_email = position('@' IN p_new_email);
email_domain_length = char_length(p_new_email) - arobase_position_in_email;
new_email_domain = substring( p_new_email from (arobase_position_in_email + 1) for email_domain_length);
-- new_email_domain = domain-name.com

Transaction

isolation level

3 levels:

• read committed: transaction T1 will not see T2 data before T2 has commited
• repeatable read (defauklt): transaction T1 will always read the same data until T1 has commited (even if T2 has changed it it the meanwhile)
• serializable: transaction T1 cannot update data if these have been modified in the meanwhile (an error "could not serialize access" is thrown)

To chnage it: SET SESSION <ISOLATION_LEVEL>

Syntax

Transaction starts with BEGIN

Simple transaction

\set AUTOCOMMIT off 
TRUNCATE TABLE recipe;
COMMIT;
BEGIN;
INSERT INTO recipe(name, serving, source) values ('bread', 2, 'freshloaf.com');
COMMIT; 
INSERT INTO recipe(name, serving, source) values ('curry', 4, 'my-curry.com');
ROLLBACK;
SELECT * FROM recipe; -- shows bread, but no curry

Complex transaction

\set AUTOCOMMIT off 
TRUNCATE TABLE recipe;
COMMIT;
BEGIN;
INSERT INTO recipe(name, serving, source) values ('bread', 2, 'freshloaf.com');
SAVEPOINT bread;
INSERT INTO recipe(name, serving, source) values ('curry', 4, 'my-curry.com');
SAVEPOINT curry;
ROLLBACK TO bread;
SELECT * FROM recipe; -- shows bread, but no curry

Message current transaction is aborted, commands ignored until end of transaction block points out an error has occured and you didn't rollback. PG will not run any other query, you'll have to ROLLBACK.

Logging

API settings:

• how
  • logging_collector=on/off
    • log_directory=PATH
    • log_filename=PATTERN
• where
  • log_destination=stderr/csvlog
• details
  • log_duration=on/off
  • log_error_verbosity=TERSE/DEFAULT/VERBOSE
• what
  • log_connections=on/off
  • log_statement=dml/mod/all
  • log_lock_waits=on/off
  • log_temp_files=0
  • log_replication_commands=on/off

All-including in file: -c logging_collector=on -c log_destination=stderr -c log_directory=/tmp -c log_duration=on -c log_error_verbosity=VERBOSE -c log_connections=on -c log_statement=all -c log_lock_waits=on -c log_temp_files=0 -c log_replication_commands=on

Verbose connection/statement/locks in file: -c logging_collector=on -c log_destination=stderr -c log_directory=/tmp -c log_duration=on -c log_error_verbosity=VERBOSE -c log_connections=on -c log_statement=all -c log_lock_waits=on

Verbose statement in file: -c logging_collector=on -c log_destination=stderr -c log_directory=/tmp -c log_duration=on -c log_error_verbosity=VERBOSE -c log_statement=all

Docker

setup logs

container

Pass on command-line using -c SETTING docker container run -d postgres:alpine -c logging_collector=on -c log_destination=stderr -c log_directory=/tmp -c log_statement=all

docker-compose

Pass on command-line using -c SETTING In docker-compose.yml

image: postgres:alpine
    command: postgres -c logging_collector=on -c log_destination=stderr -c log_directory=/tmp -c log_statement=all

access logs

List:

• get log name: docker exec -it CONTAINER_ID ls /tmp/postgresql-*.log
• watch log: docker exec -it CONTAINER_ID tail -f /tmp/postgresql-2020-06-13_142349.log

Session time

Untested:

Delete data

Some rows

List:

• no dependencies: DELETE FROM TABLE foo WHERE id = 1;
• dependencies: DELETE FROM TABLE foo WHERE id = 1 CASCADE;

A whole table

List:

• no dependencies (DELETE privilege, slow, no space reclaim without VACUUM): DELETE FROM TABLE foo;
• no dependencies (TRUNCATE privilege, ACCESS EXCLUSIVE lock): TRUNCATE TABLE foo;
• dependencies: TRUNCATE TABLE foo CASCADE;

TRUNCATE may have unintended consequences regarding transactions, check the docs

A single row and its dependencies

Script here

Lint

List: - static, no DB / python: pgsanity