https://www.postgresql.org/docs/11/datatype.html
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;
select pg_size_pretty (pg_indexes_size('my_table'));
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
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>
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;
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 or replace procedure logger() as $$
begin
raise notice '#### message from logger';
end;
$$ language plpgsql;
call logger();
select
table_name,
column_name,
data_type
from
information_schema.columns
where
table_schema = 'my_schema' and
table_name = 'my_table'
\x
select * from pg_stat_activity where datname = 'my_database';
select
pg_terminate_backend(pid)
from
pg_stat_activity
where
pid = '18765';
\c database