PostgreSQL | Features | @ Docker | Wiki | Wikipedia

PostgreSQL Server Parameters | Tuning Guide

Searchable listing and descriptions of all PostgreSQL server parameters, and tuning guide too!

psql session

$ psql

psql (12.7)
postgres=# \q

$ createuser -P -e user1
$ psql -c "CREATE DATABASE foo"
$ psql -U user1 -d foo

psql (12.7)
foo=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 user1     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
foo=> \q

PostgreSQL 12+ @ Docker

@ AWS RDS :: Managed RDBMS Engine per AMI Instance

# Versions list
aws rds describe-db-engine-versions \
    --engine 'postgres' \
    | jq -r '.DBEngineVersions[].EngineVersion'

Architecture

PostgreSQL uses a client/server model; a session consists of cooperating processes:

Data Definition Language (DDL)

Data Types @ PostgreSQL | @ SQL (Reference) | Extensible types

System Info Functions

Pairing types across app boundaries

PostgreSQL Golang Javascript
BIGINT int integer
UUID string string
NUMERIC(5,2) ??? ???
TIMESTAMPTZ time.Time string

Comments

Mind the syntax; always only one -- per line, and always follow by at least one whitespace before adding text.

-- okay
SELECT foo, bar FROM a -- okay
-- okay
--bad
---bad
-- bad -- bad
WHERE bar = 22;    -- okay

Types

SQL | Commands | Language | Tutorial

SQL Keywords

Both "reserved" + "non-reserved" words to avoid as identifiers. E.g., id, name, names, new, nil, none, null, nulls, path, per, ref, row, …, uri, user, value, view, views

Admin :: CREATE/DROP Database / User GRANT ... PRIVILEGES

-- drop (remove, delete) db
DROP DATABASE 'db_foo';
-- create a db
CREATE DATABASE 'db_foo';
-- create user
CREATE USER 'userof_foo' WITH PASSWORD 'pass_of_userof_foo';
-- grant privileges
GRANT ALL PRIVILEGES ON DATABASE 'db_foo' to 'userof_foo';
-- revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE company from james;

-- alter
ALTER USER james WITH SUPERUSER;
ALTER USER james WITH NOSUPERUSER;
-- remove
DROP USER james;

-- server version
SELECT version();

-- connect to db
\c db_foo
-- list dbs
\l 
-- see current user
SELECT current_user;
-- see current database
SELECT current_database();

Benchmark :: EXPLAIN [ANALYZE, ...]

EXPLAIN ANALYZE 
    SELECT b.*
    FROM books b 
    WHERE price < 8
    ORDER BY b.title ASC;

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Sort  (cost=25.14..25.64 rows=200 width=108) (actual time=0.017..0.017 rows=2 loops=1)
   Sort Key: title
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on books b  (cost=0.00..17.50 rows=200 width=108) (actual time=0.008..0.010 rows=2 loops=1)
         Filter: (price < '8'::numeric)
         Rows Removed by Filter: 1
 Planning Time: 0.056 ms
 Execution Time: 0.029 ms
(8 rows)

Create Schema

Schema : Best Practices

Add/set schema name to user's name. Also run certain REVOKE commands so that any current user's default schema is their user name (instead of default being public).

-- Create schema
CREATE SCHEMA IF NOT EXISTS uzr1;
--SET search_path TO uzr1, public;
ALTER SCHEMA uzr1 OWNER TO CURRENT_USER;

-- Revoke CREATE permission on public schema from PUBLIC role 
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- Revoke PUBLIC role’s ability to connect to database
REVOKE ALL ON DATABASE db1 FROM PUBLIC;

Data Manipulation (CRUD)

Create Table

Better, two-step method;

  1. Create table(s)

  2. Add constraints (Referential Integrity)

    -- 1.) Create table
CREATE TABLE products_orders (
product_id BIGINT,
order_id BIGINT
);

-- 2.) Add Constraints (Referential Integrity)
--     Naming each CONSTRAINT allows for subsequent DROP CONSTRAINT <name>;

ALTER TABLE products_orders
ADD CONSTRAINT product_id_fk 
    FOREIGN KEY (product_id) RENCES products(product_id) ON DELETE RESTRICT;

ALTER TABLE products_orders
ADD CONSTRAINT order_id_fk  
    FOREIGN KEY (order_id) RENCES orders(order_id) ON DELETE CASCADE;

ALTER TABLE products_orders
ADD CONSTRAINT products_orders_pk PRIMARY KEY (product_id, order_id);

SELECT ... CASE ... LIMIT

...
SELECT *
FROM "vw_messages"
...
ORDER BY 
    CASE WHEN n  > 0 THEN date_create END DESC, -- @ older
    CASE WHEN n <= 0 THEN date_create END ASC   -- @ newer
LIMIT CASE WHEN n > 0 THEN n ELSE -n END;

Insertions (INSERT) – Populate a table with values, per table row.

INSERT ... VALUES (..)

-- Insert a row whereof all columns are set to default values
INSERT INTO products DEFAULT VALUES;
-- Insert a row per data set of declared values; columns IMPLIED
INSERT INTO products VALUES (1, 'Cheese', 9.99);
-- Insert a row per data set of declared values; columns EXPLICITLY declared
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
-- Insert SEVERAL ROWS, per explicitly declared columns
INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

INSERT ... SELECT ...

Populate one table (table2) with values from another table (table1), column per column.

INSERT INTO table2 (column1, column2, column3, ...)
    SELECT column1, column2, column3, ...
    FROM table1
    WHERE condition;

Static test

INSERT INTO messages (
    to_id, to_handle, chn_id, body, author_id)
SELECT 'toMsgID', 'toAuthorHandle', 'chnID', 'the body', 'authorID'
WHERE CASE
    WHEN 
        'toMsgID' = ''
    THEN 
        'chnID' IN ( SELECT c.chn_id FROM vw_channels c WHERE c.owner_id = 'authorID' )
    ELSE 
        true
    END
LIMIT 1

More examples …

-- Insert into tbl_1 values from tbl_2 plus literal(s)
INSERT INTO tbl_1 (
    xid, scope, key_name, key_hash
) -- user_id is field of tbl_2; all else are literals. 
SELECT user_id, xis_user_enum(), 'rand26.rand60', pw_hash('rand26.rand60')
FROM tbl_2
WHERE roles @> ARRAY['HOST']
AND (email LIKE '%@emx.unk')
-- Insert conditionally, including such at other tables (Golang sqlx syntax)
INSERT INTO messages (
    msg_id, to_id, to_display, to_handle, chn_id, 
    author_id, author_display, author_handle, author_avatar, author_badges, 
    title, summary, body, form, privacy, date_create, date_update, sponsub)
SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18
WHERE CASE -- <<< Insert(s) occur only where CASE evalutes to true, per EXISTS(..).
    WHEN -- new (not reply) message.
        ( $2 )::UUID IS NULL
    THEN -- forbid new message lest author is channel owner, or is auto-generated sponsub message.
        EXISTS (
            SELECT 1
            FROM vw_channels c 
            WHERE c.chn_id = $5
            AND CASE 
                    WHEN $18 <> 0
                    THEN true
                    ELSE c.owner_id = $6
                END
        )
    ELSE -- forbid reply message lest recipient message (rx) exists in channel.
        EXISTS (
            SELECT 1 
            FROM vw_messages rx 
            WHERE rx.msg_id = $2
            AND rx.author_handle = $4
            AND rx.chn_id = $5
        )
    END
LIMIT 1

Upsert

-- ON CONFLICT ... DO UPDATE ...
INSERT INTO geo_cities(
    id, country_id, city_name
)
VALUES (1, 1, 'ExampleName')
ON CONFLICT (id) DO UPDATE SET 
    country_id = excluded.country_id, 
    city_name = excluded.city_name
RETURNING *; -- @ function, must match pre-body statement: RETURNS ... AS

-- SELECT ... WHERE NOT EXISTS : (Golang example)
INSERT INTO channels (
    chn_id, view_id, owner_id, 
    host, slug, title, about, 
    privacy, msg_size, date_create, etag
    )
SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
WHERE NOT EXISTS (SELECT slug FROM channels WHERE slug = $5)

-- RETURNING : @ Upsert
INSERT INTO applicants (
    email, handle, code, mode, date_create
)
VALUES ('foo@bar.com', 'hsle', '1234567', 1, now())
ON CONFLICT (email) DO UPDATE SET 
    "handle" = 'hsle',
    "code" = '1234567',
    "mode" = 1,
    "date_create" = now()
WHERE applicants.email = 'foo@bar.com'
RETURNING *;          -- Typed return !!!

 idx |    email    | handle |  code   | mode |          date_create
-----+-------------+--------+---------+------+-------------------------------
  10 | foo@bar.com | hsle   | 1234567 |    1 | 2022-02-03 14:53:53.614682+00
(1 row)
-- OR
...
RETURNING applicants; -- Untyped return !!!

                           applicants
-----------------------------------------------------------------
 (10,foo@bar.com,hsle,1234567,1,"2022-02-03 14:55:43.501578+00")
(1 row)

IN is faster than OR

-- Replace this ...
WHERE (handle = 'app') OR (handle = 'AdminTest') OR (handle = 'UserTest')
--- ... with this ...
WHERE handle IN ('app', 'AdminTest', 'UserTest')

-- inverse
WHERE slug NOT IN ('pub', 'sub')

t1.c3 = ANY (..) | t1.c3 IN (..)

WHERE chn_id = ANY (SELECT chn_id FROM vw_messages WHERE author_id = appid())
-- OR (EQUIVALENT)
WHERE chn_id IN (SELECT chn_id FROM vw_messages WHERE author_id = appid())

ANY @ Array

--- Replace this ...
WHERE c.key = ANY (ARRAY[123, 539, ...])
-- ... OR ...
WHERE c.key IN (123, 539, ... )
--- ... with this (performant) ...
WHERE c.key = ANY (VALUES (123), (539), ... )
-- ... OR ...
WHERE c.key IN (VALUES (123), (539), ... )

ARRAY contains : @> | ARRAY Functions

Add an element to an ARRAY, e.g., to a field of type TEXT[]

-- Idempotent append
UPDATE users SET 
    roles = array_append(roles, 'MODERATOR')
WHERE handle = 'FooBAR' AND NOT roles @> ARRAY['MODERATOR'];

INSERT... RETURNING <table or column(s) name(s)>, or UPDATE ... RETURNING ...; returns only the rows successfuly inserted; use to validate the mutation …

RETURNING <table-name>; return ALL table columns, i.e., pointfree style …

INSERT INTO books (isbn, title, author, price) VALUES
    ('978-1505255607', 'The Time Machine', 'H. G. Wells', 5.99),
    ('978-1503261969', 'Emma', 'Jayne Austen', 9.44)
    RETURNING books;

Pointfree makes for cleaner, more legible code, and much less of it. Just be careful to match the two boundaries (db & app); the table columns (db) must align with the destination struct fields (app).

COPY ... FROM/TO ...

Insert data from/to a file source/target; requires absolute path of the file; process an entire data file in one command; several file formats (TEXT, CSV, or BINARY); less flexible than INSERT, but incurs significantly less overhead for large data loads; BINARY format is the fastest.

Requires a user having SUPERUSER or pg_write_server_files ROLE, else per script psql ... -f exfiltrate.sql

generate_series() for data generation | Set-based vs FOR LOOP
DROP TABLE IF EXISTS foo;
CREATE TABLE IF NOT EXISTS foo (
    idx     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    c1      INT
);

INSERT INTO foo (c1)  
    SELECT * FROM generate_series(105,109) -- 105, ..., 109
    LIMIT  15000;  -- safety

SELECT * FROM foo;

--  idx | c1
-- -----+-----
--    1 | 105
--    2 | 106
--    3 | 107
--    4 | 108
--    5 | 109

Queries (SELECT)

-- expression 
SELECT city, (temp_hi+temp_lo)/2  
    AS temp_avg, date FROM weather;

-- qualify
SELECT * FROM weather
    WHERE city = 'San Francisco' 
    AND prcp > 0.0;

-- unique & sort
SELECT DISTINCT city
    FROM weather
    ORDER BY city;

-- return the first row that matches
SELECT * FROM foo WHERE idx > 3 LIMIT 1;

UNION [ALL]

Concatenate tables (having same columns) per row.

SELECT * FROM messages where author_name = 'foo'
UNION
SELECT * FROM messages where to_id IS NOT NULL

UNION ALL is faster; sans redundancy check.

SELECT city FROM customers
UNION ALL -- rows may be duplicates
SELECT city FROM suppliers
ORDER BY city;

UNION is also useful per column, e.g., prepend a column to a query result

```sql 
SELECT 'Customer' AS kind, contact_name, city, country
FROM customers
UNION
SELECT 'Supplier', contact_name, city, country
FROM suppliers;

kind        contact_name        city            country
Customer    Yvonne Moncada      Buenos Aires    Argentina 
Customer    Zbyszek             Walla           Poland 
Supplier    Anne Heikkonen      Lappeenranta    Finland 
Supplier    Antonio del Sadra   Oviedo          Spain 
Supplier    Beate Vileid        Sandvika        Norway 
```

JOIN

Combine tables per common column | postgresql.org | w3schools.com | @w3resource.com

Join Types

Updates (UPDATE)

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

WHERE col_name IN ('val1', 'val2', ...)

UPDATE users SET 
    pass_hash = pw_hash('abc123'),
    date_update = now()
    WHERE handle IN ('foo', 'bar', 'boofar');

Deletions (DELETE)

DELETE FROM weather WHERE city = 'Hayward';

Greatly simplies CRUD logic of external code.

Conditional DELETE

Delete record(s) of a table per result of a query upon another table. The following example deletes all old short-form messages of no interest to any other member AND authored by any member having no buyin. That last "AND" is conditional upon read result, EXISTS(SELECT...), from logic upon another table (users).

DELETE FROM messages 
WHERE CURRENT_TIMESTAMP - date_update > 10 * interval '1 hour'
AND count_replies = 0
AND repubs = 0
AND sponsub = 0
AND tokens_q <= 0
AND tokens_p = 0
AND size < 1024
AND EXISTS (
    SELECT idx FROM users 
    WHERE users.user_id = messages.author_id
    AND users.acc_buyin = 0
);

Junction Table to Resolve Many-to-Many Relationships

M:M  ==>  M:1>--<1:M

-- M:M @ products:orders
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

-- resolve (w/ constraints) @ junction table
CREATE TABLE order_items (
    product_no integer RENCES products ON DELETE RESTRICT,
    order_id integer RENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Variables | psql meta-commands

\set key1 val1

Use @ :key1, :'key1', or (:key1)

-- :key1
\set idx 5
DELETE FROM users where user_id = :idx;

-- :'key1'
\set cid 'f4a21a19-5fb8-4e5d-ac89-b54f4bd5c81f'
SELECT get_msglist_chan_json(:'cid', now(), 99);

-- (:key1)
\set cid 'select distinct chan_id from messages where body = \'__FAUX__\''
select get_msglist_chan_json((:cid), now(), 99)

CTE (Common Table Expressions) AKA WITH Queries

WITH fooVar AS (query) provides a way to write auxiliary statements for use in a larger query, for improved performance; define temporary tables that exist just for one query, and set its result to a variable. CTEs may be utilized to perform some subset of what otherwise requires a PostgreSQL trigger function.

-- Two CTEs; the second utilizing the first. 
WITH regional_sales AS ( -- CTE; sets its query result to `regional_sales` var
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), 
     top_regions AS ( -- CTE; sets its query result to `top_sales` var 
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
-- Main query; access var `regional_sales` 
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions) 
GROUP BY region, product;

WITH RECURSE

The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output.

SELECT DISTINCT mm.* FROM ( 
    SELECT m1.* FROM vw_messages m1 
    WHERE tokens_q > 0

    UNION ALL

    SELECT m2.* FROM vw_messages m2 
    WHERE tokens_p > 0
) mm
ORDER BY mm.tokens_p, mm.tokens_q DESC
LIMIT 20;

SELECT DISTINCT mm.* FROM ( 
    WITH RECURSIVE xx AS(

        SELECT tx.* 
        FROM vw_messages tx 
        JOIN vw_messages rx
        ON tx.to_id = rx.msg_id        
        AND rx.author_id = uid   

        UNION ALL

        SELECT mx.*
        FROM vw_messages mx
        JOIN xx ON mx.to_id = xx.msg_id 

    ) SELECT * FROM xx
    ...
) mm

Views

Materialized Views

CREATE UNLOGGED TABLE ...

"Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables. However, ... not crash-safe ... not replicated to standby servers ..."

Server Programming

Stored Procedures

Confusing lingo; three types of such …

Functions and Operators | SQL Functions | Control Structures

Allow us to group a block of SQL statements inside the database server; radically reduce client/server comms overhead.

Using Procedural Languages; PL/pgSQL, PL/Tcl, PL/Python, ...; loadable.

SQL vs. PL/pgSQL

-- @ SQL (standard)
CREATE OR REPLACE FUNCTION set_chan(oid INT, vid INT, frag TEXT)
    RETURNS TABLE (chan_id INT) AS
$BODY$
    INSERT INTO channels (owner_id, view_id, slug) 
        VALUES (oid, vid, frag) 
        RETURNING channels.chan_id;
$BODY$ LANGUAGE SQL;


-- @ PL/pgSQL
CREATE OR REPLACE FUNCTION set_chan(oid INT, vid INT, frag TEXT)
    RETURNS TABLE (chan_id INT) AS
$BODY$
BEGIN
    RETURN QUERY -- Else err: "query has no destination for result data"
    INSERT INTO channels (owner_id, view_id, slug) 
        VALUES (oid, vid, frag) 
        RETURNING channels.chan_id;
END
$BODY$ LANGUAGE 'plpgsql';

Control Structures

RETURN ..., RETURNING ..., IF-THEN-ELSEIF, CASE-WHEN-ELSE-END

Return Base Types

CREATE FUNCTION somefunc(integer, text) 
RETURNS integer AS 
$BODY$
BEGIN
-- ... the function body 
END
$BODY$
LANGUAGE plpgsql;

Dollar Quoting

Load a function per SQL file :: \i funcDef.sql

Return Composite types @ 1 row

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

CREATE FUNCTION double_salary(emp) 
RETURNS numeric AS 
$$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

Return TABLE @ one or more rows

Per RETURN QUERY @ plpgSQL

CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR) 
	RETURNS TABLE (
		film_title VARCHAR,
		film_release_year INT
) 
AS $$
BEGIN
	RETURN QUERY SELECT
		title,
		cast( release_year as integer)
	FROM
		film
	WHERE
		title ILIKE p_pattern ;
END; $$ 
LANGUAGE 'plpgsql';

Use a function as a TABLE source; per referenced-table name (foo)

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

Return SETOF (UNTYPED) @ one or more rows of a TABLE

How to retrieve the table rows per pointfree style

SETOF table_name

DROP FUNCTION IF EXISTS get_owner(UUID);
CREATE OR REPLACE FUNCTION get_owner(cid UUID)
    --RETURNS TABLE (user_id UUID, uname TEXT, email TEXT) AS
    RETURNS SETOF users AS
    --RETURNS SETOF users AS -- ERROR type mismatch ???
$BODY$
    SELECT u.* FROM users u
    INNER JOIN channels c
    ON u.user_id = c.owner_id
    WHERE c.chan_id = cid 
$BODY$
LANGUAGE SQL;

-- SUCCESS (return is typed)
SELECT * FROM get_owner('84944737-e5e4-4ebf-855a-cc3306781603');
-- SUCCESS (return is untyped)
SELECT get_owner('84944737-e5e4-4ebf-855a-cc3306781603');

However, we can declare a type, and reference it thereafter.

CREATE TYPE footype AS (score int, term text);

CREATE FUNCTION foo() RETURNS SETOF footype AS $$
   SELECT * FROM ( VALUES (1,'hello!'), (2,'Bye') ) t;
$$ language SQL immutable;

CREATE FUNCTION foo_tab() RETURNS TABLE (score int, term text) AS $$
   SELECT * FROM ( VALUES (1,'hello!'), (2,'Bye') ) t;
$$ language SQL immutable;

SELECT * FROM foo();      -- works fine!
SELECT * FROM foo_tab();

Return SETOF RECORD (UNTYPED) @ one or more rows of a TABLE

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR $1;
 result RECORD;
BEGIN
 RETURN QUERY SELECT '1', '2', '3';
 RETURN QUERY SELECT '3', '4', '5';
 RETURN QUERY SELECT '3', '4', '5';
END
$$;

Return per OUT params; OUT var TYPE

Useful when return is a basic type (vs a composite, TABLE, etal)

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT x + y'
LANGUAGE SQL;

SELECT add_em(3,7);

Return void

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

LATERAL : SELECT f.col_3, x.* FROM foo f, LATERAL aFunc(f.bar) x ...

Call a function on column(s) of each record in a query, returning its results (all or selected columns) instead of, or in addition to, column(s) of the queried table.

-- Insert a user-scoped api key (VIP key) for each user having ...
SELECT x.handle, x.xid, x.api_key
FROM vw_users u,
    LATERAL insert_user_scoped_api_key(u.handle) x
WHERE u.roles @> ARRAY['HOST']
AND (u.email LIKE '%@emx.unk');

  handle                       xid                                  api_key
SlowMoFTW      b03048a8-24c3-4658-b645-c7c6eecefa8b    AWTTFKRTQX4XCZLR3D6JGWLPBL.bnP...oNv
TheRetorter    bd8479d6-111c-42e3-a49d-25423535cb39    HZ4CI21144KBMRD6EEEGMJZ5TJ.JVC...Hsw
...

CURSOR @ PL/pgSQL usage in a function | @ /docs

Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time.

Trigger Functions

A function that binds to a table/event, and automatically triggers on data changes or database events. Unlike other functions, it does not accept any parameters.

-- per CTE Postgres 9.1:
WITH rows AS (
    INSERT INTO Table1 (name) VALUES ('a_title') 
    RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows

-- per Trigger:
CREATE FUNCTION t1_ins_into_t2()
    RETURNS trigger AS 
$$
BEGIN
    INSERT INTO table2 (val) 
        VALUES (NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t1_ins_into_t2
    AFTER insert on table1
FOR EACH ROW
EXECUTE PROCEDURE t1_ins_into_t2();

CREATE PROCEDURE

A kind of function sans return; unlike functions, procedures allow transactions

CREATE OR REPLACE PROCEDURE set_transaction_q(payer INT, payee INT, msg INT, q INT)
LANGUAGE 'plpgsql'    
AS $$ -- ... returns void on success; ERROR on fail
BEGIN
    -- payer (from)
    UPDATE users SET tokens_q = tokens_q - q
        WHERE user_id = payer;
    -- payee (to)
    UPDATE users SET tokens_q = tokens_q + q
        WHERE user_id = payee;
    -- record 
    INSERT INTO transactions (payer_id, payee_id, msg_id, tokens_q) 
        VALUES (payer, payee, msg, q);
    COMMIT;
END;
$$;

CALL set_transaction_q(:payer, :payee, :msg, :q);

Transaction @ PROCEDURE BEGIN ... COMMIT ... ROLLBACK

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;

CALL transaction_test1();

LOCK

This is a per table, per transaction write lock; all other users are prevented from writing to the table whilst under lock.

BEGIN;
LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;
-- All other users are now restricted to read-only mode at this table.
-- ... Do stuff to foo table and/or its constraints, then ...
COMMIT; -- Unlock the table.

PostgreSQL Tutorial

All Native PostgreSQL Client Apps

pgbench :: benchmark tests

su - postgres  # switch to 'postgres' user (@ alpine)

psql -c 'SHOW config_file'
psql -c 'SELECT version();'     # version of SERVER (postgres daemon)
psql --version                  # version of CLIENT (psql) 
# Initialize
pgbench -i -p 5432 -d postgres
#...
# Test w/ 10 clients 
pgbench -c 10  # -T <SECONDS>
#...
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 100/100
latency average = 23.646 ms
tps = 422.909536 (including connections establishing)
tps = 426.859358 (excluding connections establishing)
#... tps = Transactions Per Second 

# -S (SELECT only; read only), -n (skip vacuum)
pgbench -c 100 -T 300 -S -n
# Try tweaking params: `shared_buffers` and `effective_cache_size` 
# @ postgresql.conf
exit # back to root user
apt-get update
apt-get install vim 
su - postgres
vim /var/lib/postgresql/data/postgresql.conf

@ bench.sql (bench.init.sql)

INSERT INTO test_bench VALUES(1,'test');
INSERT INTO test_bench VALUES(1,'test');
SELECT * FROM test_bench WHERE id=1;
SELECT * FROM test_bench WHERE id=2;

psql :: client CLI utility

SQL @ host per psql

# SQL ...
psql -c '\x' -c 'SELECT * FROM foo;'
# or
echo '\x \\ SELECT * FROM foo;' | psql

SQL @ server session per psql

# Connect, then enter SQL statements directly 
$ psql -h <host> -p <port> -U <user> -W <pass> <database>

SELECT * FROM foo;

SELECT version();    # show PostgreSQL SERVER version  
SELECT current_date;
SELECT 2 + 2;

psql :: commands

su - postgres  # switch to 'postgres' user (@ alpine)

psql -c 'SHOW config_file'
psql -c 'SELECT version();'     # version of SERVER (postgres daemon) 
psql --version                  # version of CLIENT (psql) 
# Connect to database at remote host
$ psql -h <host> -p <port> -U <user> -W <pass> <database>
# Connect locally
$ psql -U <user> 
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.
<user>=>   # cmd prompt @ psql session, if restricted user
<user>=#   # cmd prompt @ psql session, if superuser (@ docker|installer)

# psql commands ...
    \h               # help   
    \l               # List databases  
    \c $DB_NAME      # Connect to database  
    \d               # Display tables list 
    \d+ $TABLE_NAME  # Display table schema (collumn names and data type)  
    \x               # Expanded display (toggle); much more verbose
    \q               # Quit; end session; terminate the forked server process

@ Docker container (postgres.docker.sh)

# @ Container shell (bash) ...

root@f3e668efe2a2:/home# pushd home 
root@f3e668efe2a2:/home# psql -U postgres -f ./sql/wipe.sql
root@f3e668efe2a2:/home# psql -U postgres -f ./sql/migrate.sql
root@f3e668efe2a2:/home# psql -U postgres -f ./sql/seeds.sql

root@f3e668efe2a2:/home# psql -U postgres -c 'SELECT * from topics'
root@50a7156ab294:/home# psql -U postgres -c 'SELECT owner_id as id, slug as path FROM channels'

# @ Launch INTERACTIVE client session (PostgreSQL)

root@f3e668efe2a2:/home# psql -U postgres

@ interactive session …

postgres=# \l
postgres=# \d
postgres=# \d+ channels 

postgres=# SELECT owner_id AS id, slug AS path FROM channels;
                  id                  |  path
--------------------------------------+--------
 45b5fbd3-755f-4379-8f07-a58d4a30fa2f | slug-1
 5cf37266-3473-4006-984f-9325122678b7 | slug-2
 45b5fbd3-755f-4379-8f07-a58d4a30fa2f | slug-3

Server Config

Show Location:

SHOW config_file;

Reload @ running server:

Create a database

Destroy a database

Backup/Restore a Database | Automate

@ Ubuntu/Debian

Connect the backend server

Install psql @ Ubuntu

$ sudo apt install postgresql-client-common
$ sudo apt install postgresql-client-10

Connect using psql

# Connect as root using `psql` utility
$ sudo -U postgres psql
postgres=#
# as root, set user/creds 
ALTER USER postgres WITH PASSWORD 'foobar';
# Henceforth, login ...
$ psql -U postgres -h localhost
\q  # to exit

@ Docker

Run a Postgres server container

$ docker run -d -p 5432:5432 --name 'db' \
    -e POSTGRES_PASSWORD=$dbpw postgres

Access the server

$ psql -h localhost -p 5432 -U ${POSTGRES_USER} 
Password for user postgres:
...
postgres=#

Access the server container, per shell

$ docker exec -it $CNTNR_ID bash -c "psql -U postgres"
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

postgres=#

... same, but in stages

# Access the running Postgres container per its ID
$ docker exec -it $CNTNR_ID bash 
root@b39a615060bc:/#

PostgreSQL + adminer @ docker stack or docker-compose

docker stack deploy -c 'postgres.stack.yml' 'postgres' 
# or 
docker-compose -f 'postgres.stack.yml' up
docker-compose -f 'postgres.stack.yml' down -v  # delete volume(s) too

pgAdmin @ Docker

docker run -p 8080:80  \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    --name 'pgadmin' \
    -d dpage/pgadmin4

PostgreSQL + pgAdmin @ docker stack or docker-compose