PostgreSQL cheat sheet

pgsql_cheatsheet.sql

-- 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';