Check if current connected node is Master or a Replica:
SELECT pg_is_in_recovery();
If response is true then this node is a Replica node and is in "read-only" mode.
Date time arithmetics:
select to_char( ( NOW() - INTERVAL '1 DAY' ), 'YYYYMMDDHH24MISS' );
OUTPUT: 20240819085935
How to find all tables with particular column name:
select
t.table_schema, t.table_name, c.column_name
from information_schema.tables t
inner join information_schema.columns c
on c.table_name = t.table_name and c.table_schema = t.table_schema
where upper(c.column_name) like '%PRICE%' and t.table_schema not in ('information_schema', 'pg_catalog') and t.table_type = 'BASE TABLE'
order by t.table_schema, t.table_name, c.column_name;
How to find all stored procedures which contains a particular keyword:
select proname, prosrc from pg_proc where prosrc like '%mykeyword%';
Group by and concat values:
SELECT
company_id,
string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;
Regex in where (that TILDE sign means regex):
select * FROM mytable where mac ~ '[a-zA-Z]|[1-9]';