Cheat Sheet - Postgres

Foreach table in a schema

DO
$$
DECLARE
   rec   record;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  schemaname = 'my-schema'
   LOOP
      EXECUTE 'alter table ' || quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename) || ' owner to ...';
   END LOOP;
END
$$;

Display owners

-- Show db owners
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
ORDER BY 1;

-- Show schema owners
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
  pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;

-- Show owner of a table 
select * 
from pg_tables 
where schemaname = 'my-schema';

Determining storage space

-- Get database size
select pg_size_pretty(pg_database_size('postgres'));

-- Get table size
select table_schema,
       table_name,
       pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) as size
from information_schema.tables t
where t.table_schema = 'my_schema'
  and t.table_name = 'my_table';

-- Get all tables in a schema
select table_schema,
       table_name,
       pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) as size
from information_schema.tables t
where t.table_schema = 'my_schema';

Row Level Security & Policies

 -- List policies
 
 SELECT n.nspname AS schemaname,
    c.relname AS tablename,
    pol.polname AS policyname,
        CASE
            WHEN pol.polpermissive THEN 'PERMISSIVE'::text
            ELSE 'RESTRICTIVE'::text
        END AS permissive,
        CASE
            WHEN pol.polroles = '{0}'::oid[] THEN string_to_array('public'::text, ''::text)::name[]
            ELSE ARRAY( SELECT pg_authid.rolname
               FROM pg_authid
              WHERE pg_authid.oid = ANY (pol.polroles)
              ORDER BY pg_authid.rolname)
        END AS roles,
        CASE pol.polcmd
            WHEN 'r'::"char" THEN 'SELECT'::text
            WHEN 'a'::"char" THEN 'INSERT'::text
            WHEN 'w'::"char" THEN 'UPDATE'::text
            WHEN 'd'::"char" THEN 'DELETE'::text
            WHEN '*'::"char" THEN 'ALL'::text
            ELSE NULL::text
        END AS cmd,
    pg_get_expr(pol.polqual, pol.polrelid) AS qual,
    pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check
   FROM pg_policy pol
     JOIN pg_class c ON c.oid = pol.polrelid
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace;

create table parent
(
    parent_id bigint primary key,
    oe        text not null,
    content   text
);

create table child1
(
    child1_id bigint primary key,
    parent_id bigint references parent on delete cascade,
    oe        text not null,
    content   text
);

-- trying to boost rls performance
create index on parent (oe);
create index on child1 (oe);

-- insert test data
insert into parent values (1, 'CH', 'some secret values');
insert into parent values (2, 'FR', 'some secret values');
insert into parent values (3, 'MX', 'some secret values');
insert into child1 values (1, 1, 'CH', 'CH addendum');
insert into child1 values (2, 2, 'FR', 'FR addendum');
insert into child1 values (3, 3, 'MX', 'MX addendum');

-- create roles
create role readers;
create role ch_readers;
create role fr_readers;

-- Add roles to group role
grant readers to ch_readers, fr_readers;

-- grant basic read privileges
grant select on parent, child1 to readers;  

-- create concrete users and grant corresponding roles
create user ueli with password 'pass123' role ch_readers;
create user jaques with password 'pass123' role fr_readers;

-- create policies
create policy ch_reader_policy on parent for select to ch_readers using (oe = 'CH');
create policy ch_reader_policy on child1 for select to ch_readers using (oe = 'CH');

create policy fr_reader_policy on parent for select to fr_readers using (oe = 'FR');
create policy fr_reader_policy on child1 for select to fr_readers using (oe = 'FR');

Publications & Subscriptions

version: '3'
services:
  publisher:
    image: postgres:11
    container_name: postgres-11-pub
    command: postgres -c wal_level=logical -c max_replication_slots=20
    ports:
      - 5001:5432
    environment:
      - POSTGRES_PASSWORD=postgres      

  subscriber:
    image: postgres:11
    container_name: postgres-11-sub
    ports:
      - 5002:5432
    environment:
      - POSTGRES_PASSWORD=postgres

docker-compose.yaml

-- Publisher: Create table
create table test1 (a int, b  int);

-- Publisher: Insert initial data
insert into test1 values (1, 2);
insert into test1 values (1, 2);
insert into test1 values (1, 2);

-- Publisher: Create publication without constraints
create publication pub1 for table test1;

-- Subscriber: Create initial schema
create table test1 (a int, b  int);

-- Subscriber: Create subscription
create subscription sub1
connection 'host=10.42.2.52 port=5001 dbname=postgres user=postgres password=postgres application_name=sub1'
publication pub1;

-- Subscriber: Check initial data arrived
select * from test1;
+-+-+
|a|b|
+-+-+
|1|2|
|1|2|
|1|2|
+-+-+

-- Create a publication 
CREATE PUBLICATION pub1 FOR TABLE address;
CREATE PUBLICATION pub1 FOR TABLE address, customer;

-- Create publication for all tables in the database
CREATE PUBLICATION pub1 FOR ALL TABLES;

-- List publications 
SELECT * from pg_publication;
SELECT * from pg_publication_tables;

Logical Replication Slots (test_decoding plugin)

-- Create replication slot using test_decoding plugin
-- Syntax Postgres11: pg_create_logical_replication_slot(slot_name name, plugin name [, temporary boolean])
select * from pg_create_logical_replication_slot('slot1', 'test_decoding', false);

-- Display slots
select * from pg_replication_slots;

-- insert, update, delete, ...

-- Consume changes
-- Syntax Postgres11: pg_logical_slot_get_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
select * from pg_logical_slot_get_changes('slot1', null, null);

-- Remove replication slot
select * from pg_drop_replication_slot('slot1');

Logical Replication Slots (pgoutput plugin)

-- Create replication slot using pgoutput plugin
-- Syntax Postgres11: pg_create_logical_replication_slot(slot_name name, plugin name [, temporary boolean])
select * from pg_create_logical_replication_slot('slot1', 'pgoutput', false);

-- Display slots
select * from pg_replication_slots;

-- insert update delete, ...

-- Consume changes, requires a publication
-- Syntax Postgres11: pg_logical_slot_get_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
select * from pg_logical_slot_get_binary_changes('slot1', null, null, variadic options := array['proto_version', '1', 'publication_names', 'pub1']);

-- Remove replication slot
select * from pg_drop_replication_slot('slot1');

Date & time notes

-- select part of the date to operate with 
select distinct date_part('year', d.some_date)
from some_table as t
where date_part('year', t.some_date) between 2019 and 2022;

-- Cut of milliseconds from timestamp 
select current_timestamp(0);
select current_timestamp::timestamp(0); -- 2022-09-26 07:15:41

-- Custom formatting 
select to_char(current_timestamp, 'YYYY-MM-DD HH:MI'); -- 2022-09-26 07:31
select to_char(current_timestamp, 'YYYY-MM-DD HH:MI')::timestamp; -- 2022-09-26 07:31:00.000000

-- Truncate date
select date_trunc('minute', current_timestamp); -- 2022-09-26 07:36:00.000000 +00:00

List available and installed extensions

select *
from pg_available_extensions as e
order by e.name;

Installing extensions

-- dedicated schema 
create schema extensions; 

-- install the extension 
create extension tablefunc with schema extensions cascade;

-- make sure everybody can use everything in the extensions schema
grant usage on schema extensions to public;
grant execute on all functions in schema extensions to public;

-- include future extensions
alter default privileges in schema extensions grant usage on types to public;
alter default privileges in schema extensions grant execute on functions to public;

-- usage 
set search_path = my_schema, extensions; 

-- ...

Setting the search path

set search_path = schema1, schema2, extensions, public; 

-- Wrong!!
set search_path = 'schema1, schema2, extensions, public'; 
set search_path = 'schema1; schema2; extensions; public';

Transposing tables with crosstab

create table demo.evaluations
(
    student text,
    subject text,
    evaluation_result decimal (3,1),
    evaluation_day date
);

insert into demo.evaluations values ('John', 'Music', 7.0, '2016-03-01'::date);
insert into demo.evaluations values ('John', 'Maths', 4.0, '2016-03-01'::date);
insert into demo.evaluations values ('John', 'History', 9.0, '2016-03-22'::date);
insert into demo.evaluations values ('John', 'Language', 7.0, '2016-03-15'::date);
insert into demo.evaluations values ('John', 'Geography', 9.0, '2016-03-04'::date);

insert into demo.evaluations values ('Peter', 'Music', 2.0, '2016-03-01'::date);
insert into demo.evaluations values ('Peter', 'Maths', 10.0, '2016-03-01'::date);
insert into demo.evaluations values ('Peter', 'History', 7.0, '2016-03-22'::date);
insert into demo.evaluations values ('Peter', 'Language', 4.0, '2016-03-15'::date);
insert into demo.evaluations values ('Peter', 'Geography', 10.0, '2016-03-04'::date);

-- source_sql must return 3 columns
-- row_name, category, value
select e.student, e.subject, e.evaluation_result
from demo.evaluations as e
order by e.student, e.subject;

select final_result.*
from demo.crosstab(
    'select e.student, e.subject, e.evaluation_result from demo.evaluations as e order by e.student, e.subject')
as final_result(Student text, Geography numeric, History numeric, Language numeric, Maths numeric, Music numeric);

-- category sql must return at least 1 column and no duplicate values
select distinct e.subject
from demo.evaluations as e;

select final_result.*
from demo.crosstab(
    'select e.student, e.subject, e.evaluation_result from demo.evaluations as e order by e.student, e.subject',
    'select distinct e.subject from demo.evaluations as e')
 as final_result(student text, history numeric, language numeric, music numeric, geography numeric, maths numeric);

Querying JSON columns

select t.column::json->'key'->'subkey`
from my_schema.my_table as t

# Produces the same result as above 
select t.column::json#>'{key,subkey}'
from my_schema.my_table as t

# Can also be used in a where clause
select t.* 
from my_schame.my_table as t 
where (t.column::json#>'{key,subkey}')::text = 'some_search_string'

Show owner of a table

select * 
from pg_tables
where tablename = 'your_table'; 

select * 
from pg_tables
where tableowner = 'role';

Datatypes

https://www.postgresql.org/docs/11/datatype.html

Find all numeric columns in a PostgreSQL database

select col.table_schema,
       col.table_name,
       col.ordinal_position as col_id,
       col.column_name,
       col.data_type,
       col.numeric_precision,
       col.numeric_scale
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.data_type in ('smallint', 'integer', 'bigint', 
                        'decimal', 'numeric', 'real', 'double precision',
                        'smallserial', 'serial', 'bigserial', 'money')
      and col.table_schema not in ('information_schema', 'pg_catalog')
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Determine index size

select pg_size_pretty (pg_indexes_size('my_table'));

Filter for a date only in a timestamp field

select p.id, p.version, p.content_size, p.creation_date_time
from my_table.portfolio as p
where p.creation_date_time::date = date '2021-12-07'
order by p.creation_date_time desc

Multiple counts in a single query

select 
    count(*) as total,
    sum(case when line_of_business = 'Property' then 1 else 0 end) as prop_total,
    sum(case when line_of_business = 'Liability' then 1 else 0 end) as liab_total
from <table>

Transactions

begin transaction; 
    insert into public.company (name, age, salary) values ('x', 32, 20.00); 
    
    savepoint "s1"; 
    begin subtransaction; 
        insert into public.company (name, age, salary) values ('y', 10, 10.00); 
    commit; 
commit;

Functions

create or replace function total_records() returns integer as $total$
declare
    total integer; 
begin
    select count(*) into total from public.company;
    return total;
end; 
$total$ language plpgsql;

Create procedure

create or replace procedure logger() as $$
begin 
    raise notice '#### message from logger';
end;
$$ language plpgsql;

call logger();

Describe table

select 
    table_name,
    column_name,
    data_type
from 
    information_schema.columns
where 
    table_schema = 'my_schema' and 
    table_name = 'my_table'

Show active sessions

\x
select * from pg_stat_activity where datname = 'my_database';

Kill database session

select 
    pg_terminate_backend(pid) 
from 
    pg_stat_activity
where 
    pid = '18765';

Switch database

\c database