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:00List 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