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