· 9 years ago · Oct 06, 2016, 07:10 AM
1-- QUERIES OPERATIONS
2----------------------------------------------------------------------------------
3
4-- KILL ALL sessions FOR A DATABASE
5SELECT pg_terminate_backend(pg_stat_activity.pid)
6FROM pg_stat_activity
7WHERE pg_stat_activity.datname = 'TARGET_DB'
8 AND pid <> pg_backend_pid();
9
10-- GETTING RUNNING QUERIES, version: 9.2+
11SELECT datname, usename, pid, client_addr, waiting,
12 query_start, query, state
13FROM pg_stat_activity
14ORDER BY query_start DESC
15
16-- DATABASE OPERATIONS
17-----------------------------------------------------------------------------------
18-- CREATING A DATABASE OWNER
19CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypass';
20-- there are utilities in Postgres bin directory for creating users and databases
21-- dropdb -h localhost -U postgres datawarehouse
22-- createdb -e -E UTF8 -O habble -h localhost -U postgres datawarehouse
23CREATE DATABASE mydb WITH OWNER myuser ENCODING 'UTF8';
24GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
25
26-- DUPLICATE A DATABASE
27CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
28
29-- RENAME A DATABASE (no connection to olddb required)
30ALTER DATABASE "olddb" RENAME TO newdb;
31ALTER DATABASE "newdb" OWNER TO myuser
32
33
34-- TABLES OPERATIONS
35---------------------------------------------------------------------------------
36
37-- MANAGING SEQUENCES
38ALTER SEQUENCE payments_id_seq START WITH 22; -- set default
39ALTER SEQUENCE payments_id_seq RESTART; -- without value
40ALTER SEQUENCE payments_id_seq RESTART WITH 22;
41SELECT setval('payments_id_seq', 22, FALSE);
42
43-- create a sequence
44-- 1st param: table name, 2nd param: sequence name, 3rd param: sequence's owner
45CREATE OR REPLACE FUNCTION create_sequence(tbname text, seqname text, owner_seq text)
46 RETURNS text AS
47$BODY$
48DECLARE
49 r record;
50 sql text;
51BEGIN
52 execute 'DROP SEQUENCE IF EXISTS ' || seqname;
53
54 -- temporary table for tbname's children tables
55 sql:='SELECT MAX(id)+1 AS id FROM ' || tbname;
56 execute sql into r;
57
58 sql:='CREATE SEQUENCE ' || seqname || ' INCREMENT 1 MINVALUE 1 START ' || r.id;
59 execute sql;
60
61 execute 'ALTER TABLE ' || seqname || ' OWNER TO ' || owner_seq;
62 execute 'GRANT ALL ON SEQUENCE ' || seqname || ' TO ' || owner_seq;
63 return sql || ' [OK]';
64END;
65$BODY$
66 LANGUAGE plpgsql VOLATILE
67 COST 100;
68ALTER FUNCTION create_sequence(text, text, text) OWNER TO postgres;
69
70-- CREATE a SERIAL LIKE SEQUENCE FOR AN EXISTING TABLE
71CREATE OR REPLACE FUNCTION create_serial(from_schemaname text, tbname text, column_name text)
72 RETURNS text AS
73$BODY$
74DECLARE
75 r record;
76 sql text;
77 seqname text := tbname || '_' || column_name || '_seq';
78BEGIN
79 -- temporary table for tbname's children tables
80 sql:='SELECT MAX(id)+1 AS id FROM ' || from_schemaname || '.' || tbname;
81 execute sql into r;
82
83 sql:='CREATE SEQUENCE ' || seqname || ' INCREMENT 1 MINVALUE 1 START ' || r.id;
84 execute sql;
85 raise notice '%',sql;
86
87 sql:='ALTER TABLE ' || tbname || ' ALTER COLUMN ' || column_name || ' SET DEFAULT nextval(''' || seqname || ''')';
88 execute sql;
89 raise notice '%',sql;
90
91 sql:='ALTER TABLE ' || tbname || ' ALTER COLUMN ' || column_name || ' SET NOT NULL';
92 execute sql;
93 raise notice '%',sql;
94
95 sql:='ALTER SEQUENCE ' || seqname || ' OWNED BY ' || tbname || '.' || column_name;
96 execute sql;
97 raise notice '%',sql;
98
99 return '[OK]';
100END;
101$BODY$
102 LANGUAGE plpgsql VOLATILE
103 COST 100;
104
105
106-- find references to a table column
107CREATE OR REPLACE FUNCTION get_ref_table(
108 schema_name text,
109 tab_name text,
110 col_name text)
111RETURNS SETOF text AS
112$BODY$
113select R.TABLE_NAME
114from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
115inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
116 on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
117 and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
118 and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
119inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
120 ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
121 AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
122 AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
123WHERE U.COLUMN_NAME = col_name
124 AND U.TABLE_SCHEMA = schema_name
125 AND U.TABLE_NAME = tab_name;
126$BODY$
127 LANGUAGE sql;
128
129
130-- FINDING CHILDREN TABLES' NAME OF A PARTITIONED TABLE
131SELECT C.relname FROM
132 (SELECT I.inhrelid
133 FROM pg_inherits I
134 inner JOIN pg_class C ON (I.inhparent=C.oid)
135 AND C.relname='PARENT_TABLE_NAME' AND C.relkind='r') V
136 inner JOIN pg_class C ON (V.inhrelid=C.oid)
137ORDER BY 1
138
139-- FINDING CONSTRAINTS OF A PARTICULAR TABLE
140SELECT pg_get_constraintdef(P1.oid) AS condef,conname
141FROM pg_constraint P1
142 inner JOIN pg_class p2 ON (P1.conrelid=P2.oid)
143WHERE P2.relname='chiamata' AND contype in ('f')
144
145
146-- JSON FUNCTION (since 9.3) ------------------------------------------------------------
147-- if you have JSON saved in a TEXT field (maybe 'cause you have 9.2 which
148-- does not support json datatype), you can retrieve how many elements contains
149-- a key with this
150SELECT json_array_length(tablefield::json->'myKey')
151FROM table WHERE id = 317;
152
153-- it returns a SETOF json
154SELECT json_array_elements(json_extract_path(tablefield::json, 'myKey'))
155FROM table WHERE id_user = 317;
156
157-- EXTENSIONS AND FUNCTIONS
158-- finding all extensions installed in a schema
159SELECT extname
160FROM pg_extension ex
161 JOIN pg_namespace n ON ex.extnamespace = n.oid
162WHERE nspname = 'public';
163
164-- finding all your functions (if you write'em in a different language from C)
165select
166 pp.proname,
167 pl.lanname,
168 pn.nspname,
169 pg_get_functiondef(pp.oid)
170from pg_proc pp
171inner join pg_namespace pn on (pp.pronamespace = pn.oid)
172inner join pg_language pl on (pp.prolang = pl.oid)
173where pl.lanname NOT IN ('c','internal')
174 and pn.nspname NOT LIKE 'pg_%'
175 and pn.nspname <> 'information_schema';