Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL cheat sheet
-- QUERIES OPERATIONS
----------------------------------------------------------------------------------
-- KILL ALL sessions FOR A DATABASE
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
AND pid <> pg_backend_pid();
-- GETTING RUNNING QUERIES, version: 9.2+
SELECT datname, usename, pid, client_addr, waiting,
query_start, query, state
FROM pg_stat_activity
ORDER BY query_start DESC
-- DATABASE OPERATIONS
-----------------------------------------------------------------------------------
-- CREATING A DATABASE OWNER
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypass';
-- there are utilities in Postgres bin directory for creating users and databases
-- dropdb -h localhost -U postgres datawarehouse
-- createdb -e -E UTF8 -O habble -h localhost -U postgres datawarehouse
CREATE DATABASE mydb WITH OWNER myuser ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
-- DUPLICATE A DATABASE
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
-- RENAME A DATABASE (no connection to olddb required)
ALTER DATABASE "olddb" RENAME TO newdb;
ALTER DATABASE "newdb" OWNER TO myuser
-- TABLES OPERATIONS
---------------------------------------------------------------------------------
-- MANAGING SEQUENCES
ALTER SEQUENCE payments_id_seq START WITH 22; -- set default
ALTER SEQUENCE payments_id_seq RESTART; -- without value
ALTER SEQUENCE payments_id_seq RESTART WITH 22;
SELECT setval('payments_id_seq', 22, FALSE);
-- create a sequence
-- 1st param: table name, 2nd param: sequence name, 3rd param: sequence's owner
CREATE OR REPLACE FUNCTION create_sequence(tbname text, seqname text, owner_seq text)
RETURNS text AS
$BODY$
DECLARE
r record;
sql text;
BEGIN
execute 'DROP SEQUENCE IF EXISTS ' || seqname;
-- temporary table for tbname's children tables
sql:='SELECT MAX(id)+1 AS id FROM ' || tbname;
execute sql into r;
sql:='CREATE SEQUENCE ' || seqname || ' INCREMENT 1 MINVALUE 1 START ' || r.id;
execute sql;
execute 'ALTER TABLE ' || seqname || ' OWNER TO ' || owner_seq;
execute 'GRANT ALL ON SEQUENCE ' || seqname || ' TO ' || owner_seq;
return sql || ' [OK]';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION create_sequence(text, text, text) OWNER TO postgres;
-- CREATE a SERIAL LIKE SEQUENCE FOR AN EXISTING TABLE
CREATE OR REPLACE FUNCTION create_serial(from_schemaname text, tbname text, column_name text)
RETURNS text AS
$BODY$
DECLARE
r record;
sql text;
seqname text := tbname || '_' || column_name || '_seq';
BEGIN
-- temporary table for tbname's children tables
sql:='SELECT MAX(id)+1 AS id FROM ' || from_schemaname || '.' || tbname;
execute sql into r;
sql:='CREATE SEQUENCE ' || seqname || ' INCREMENT 1 MINVALUE 1 START ' || r.id;
execute sql;
raise notice '%',sql;
sql:='ALTER TABLE ' || tbname || ' ALTER COLUMN ' || column_name || ' SET DEFAULT nextval(''' || seqname || ''')';
execute sql;
raise notice '%',sql;
sql:='ALTER TABLE ' || tbname || ' ALTER COLUMN ' || column_name || ' SET NOT NULL';
execute sql;
raise notice '%',sql;
sql:='ALTER SEQUENCE ' || seqname || ' OWNED BY ' || tbname || '.' || column_name;
execute sql;
raise notice '%',sql;
return '[OK]';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- find references to a table column
CREATE OR REPLACE FUNCTION get_ref_table(
schema_name text,
tab_name text,
col_name text)
RETURNS SETOF text AS
$BODY$
select R.TABLE_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE U.COLUMN_NAME = col_name
AND U.TABLE_SCHEMA = schema_name
AND U.TABLE_NAME = tab_name;
$BODY$
LANGUAGE sql;
-- FINDING CHILDREN TABLES' NAME OF A PARTITIONED TABLE
SELECT C.relname FROM
(SELECT I.inhrelid
FROM pg_inherits I
inner JOIN pg_class C ON (I.inhparent=C.oid)
AND C.relname='PARENT_TABLE_NAME' AND C.relkind='r') V
inner JOIN pg_class C ON (V.inhrelid=C.oid)
ORDER BY 1
-- FINDING CONSTRAINTS OF A PARTICULAR TABLE
SELECT pg_get_constraintdef(P1.oid) AS condef,conname
FROM pg_constraint P1
inner JOIN pg_class p2 ON (P1.conrelid=P2.oid)
WHERE P2.relname='chiamata' AND contype in ('f')
-- JSON FUNCTION (since 9.3) ------------------------------------------------------------
-- if you have JSON saved in a TEXT field (maybe 'cause you have 9.2 which
-- does not support json datatype), you can retrieve how many elements contains
-- a key with this
SELECT json_array_length(tablefield::json->'myKey')
FROM table WHERE id = 317;
-- it returns a SETOF json
SELECT json_array_elements(json_extract_path(tablefield::json, 'myKey'))
FROM table WHERE id_user = 317;
-- EXTENSIONS AND FUNCTIONS
-- finding all extensions installed in a schema
SELECT extname
FROM pg_extension ex
JOIN pg_namespace n ON ex.extnamespace = n.oid
WHERE nspname = 'public';
-- finding all your functions (if you write'em in a different language from C)
select
pp.proname,
pl.lanname,
pn.nspname,
pg_get_functiondef(pp.oid)
from pg_proc pp
inner join pg_namespace pn on (pp.pronamespace = pn.oid)
inner join pg_language pl on (pp.prolang = pl.oid)
where pl.lanname NOT IN ('c','internal')
and pn.nspname NOT LIKE 'pg_%'
and pn.nspname <> 'information_schema';
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.