Cheat Sheet - Postgres

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 …

PostgreSQL Documentation

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