cola postgres

pg_terminate_backend.sql

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

postgres-calendar.sql

with calendar as (
select
day::date
from pg_catalog.generate_series(
	current_date - interval '1 month',
	current_date,
	interval '1 day') day
)
select 
day,
count(id)
from calendar 
left join invoices on created_at::date = day
where date_trunc('month', day) = date_trunc('month', current_date)
group by day
order by day;

postgres-filter.sql

select 
	count(1) all,
	count(1) filter (where created_at is not null),
	count(1) filter (where updated_at is not null),
	count(1) filter (where deleted_at is not null)
from table

postgres-rank-over.sql

select 
quantity,
rank() over (partition by quantity order by quantity desc)
from table;


select
created_at,
count(1),
sum(count(1)) over (order by created_at)
from table