Skip to content

Instantly share code, notes, and snippets.

@mikepaszkiewicz

mikepaszkiewicz/.sql

Last active May 6, 2020
Embed
What would you like to do?
PostgreSQL Cheat Sheet
--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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.