Skip to content

PostgreSQL

Jump to bottom
GradedJestRisk edited this page Dec 11, 2024 · 1 revision

PostgreSQL

Mindmap

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:

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

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

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 = [email protected]
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 change it: SET SESSION ``<ISOLATION_LEVEL>{=html}

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

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:

Clone this wiki locally