Cheat Sheet - Postgres

Cheat Sheet - Postgres

Publications

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

Replication Slots

-- List replication slots
select * from pg_replication_slots;

-- Create logical replication slot for decoding
select * from pg_create_logical_replication_slot('slot1', 'pgoutput', false);
select * from pg_create_logical_replication_slot(slot_name := 'slot1', plugin := 'test_decoding', temporary := false);

-- -- Consume changes, 
-- 'test_decoding' plugin
select *
from pg_logical_slot_get_changes(slot_name := 'slot_name', upto_lsn := null, upto_nchanges := null, variadic options := array['pretty-print', '1']);

-- 'pgoutput' plugin (only supports binary output)
select *
from pg_logical_slot_get_binary_changes(slot_name := 'slot1', upto_lsn := null, upto_nchanges := null, variadic options := array['proto_version', '1', 'publication_names', 'pub1']);

-- Delete slot 
select * from pg_drop_replication_slot('slot_name');

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;
52.65. pg_available_extensions
52.65. pg_available_extensions The pg_available_extensions view lists the extensions that are available for installation. See also the pg_extension catalog, which shows the …

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'
9.15. JSON Functions and Operators
9.15. JSON Functions and Operators Table 9.43 shows the operators that are available for use with the two JSON data types (see …

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