PostgreSQL Cheat Sheet

.sql

--CONNECTIONS

--check all active connections
SELECT * FROM pg_stat_activity;

--kill all other active connections
SELECT
	pg_terminate_backend(pg_stat_activity.pid)
FROM
	pg_stat_activity
WHERE
	pg_stat_activity.datname = 'database_name'
	AND pid <> pg_backend_pid();


--kill all active session
SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = 'public';

--look for long running queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

--kill long running query
SELECT pg_cancel_backend(__pid__);

--get most popular queries / runtime / call count
SELECT
	query,
	calls, total_time, ROWS, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM
	pg_stat_statements
ORDER BY
	total_time DESC

--PRIVILEGES

--references cheatsheet: https://www.w3resource.com/PostgreSQL/postgresql-privileges-grant-revoke.php
--grant non-owner of table access to join
GRANT REFERENCES ON public.retreat_points TO non_owner;


--check privileges on user for a table
SELECT
    a.tablename,
    b.usename,
    HAS_TABLE_PRIVILEGE(usename,tablename, 'references') as references
    FROM pg_tables a, pg_user b
  WHERE
    a.tablename IN ('habitats', 'retreat_points')
    and b.usename = 'mutt'
  ;
  
--QUERIES:JSONB

--select nested column
SELECT
	changes
FROM
	shift_audit_logs
WHERE
	shift_id = 'db2375bc-9f27-43e3-a806-977d7c0e40ee'
	AND changes -> 'new' -> 'user_id' IS NOT NULL

--QUERIES

   --DATES
--convert from UTC to America/New_York
SELECT ((stored_timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York') AS local_timestamp
FROM my_table;

--pretty timestamp in 12 hr format + am/pm
select to_char(now(), 'MM/DD HH12:MI AM')

to_char( ((prediction_datetime AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York'), 'MM/DD HH12:MI AM') as local_time