Differences

This shows you the differences between two versions of the page.

--- dba:postgresql:postgresql_basic_querys [2022/03/22 16:33] – [Sessions] dodger
+++ dba:postgresql:postgresql_basic_querys [2024/03/15 09:18] – [show object source] dodger
@@ Line 57: / Line 57: @@
 ===== autocommit =====
-<code sql>
+*WARNING* ''\set '' has to be on lowercase: \\
-\set AUTOCOMMIT off
+\\
-</code>
+''\set AUTOCOMMIT off''
 Check:
@@ Line 108: / Line 109: @@
 ===== list schemas =====
+Sortcode:
 <code>
 \dn
+</code>
+Query:
+<code sql>
+SELECT n.nspname AS "Name",
+  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
+FROM pg_catalog.pg_namespace n
+WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
+ORDER BY 1;
 </code>
@@ Line 150: / Line 161: @@
 ===== list functions =====
+<code sql>
+\df
+</code>
+Or
+<code sql>
+SELECT n.nspname as "Schema",
+  p.proname as "Name",
+ CASE p.prokind
+  WHEN 'a' THEN 'agg'
+  WHEN 'w' THEN 'window'
+  WHEN 'p' THEN 'proc'
+  ELSE 'func'
+ END as "Type"
+FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
+WHERE pg_catalog.pg_function_is_visible(p.oid)
+      AND n.nspname <> 'pg_catalog'
+      AND n.nspname <> 'information_schema'
+ORDER BY 1, 2;
+</code>
+or complete:
+<code sql>
+SELECT n.nspname as "Schema",
+  p.proname as "Name",
+  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
+  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
+ CASE p.prokind
+  WHEN 'a' THEN 'agg'
+  WHEN 'w' THEN 'window'
+  WHEN 'p' THEN 'proc'
+  ELSE 'func'
+ END as "Type"
+FROM pg_catalog.pg_proc p
+     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
+WHERE pg_catalog.pg_function_is_visible(p.oid)
+      AND n.nspname <> 'pg_catalog'
+      AND n.nspname <> 'information_schema'
+ORDER BY 1, 2;
+</code>
+Note:
+  "Result data type" and "Argument data types" are normally multi-lined...
+==== Aggregate functions ====
 <code sql>
 \da
@@ Line 177: / Line 235: @@
 ===== list objects in schema =====
-Change ''<SCHEMA_NAME>'':
+Change ''<SCHEMA_NAME>'' :
 <code sql>
 select n.nspname as schema_name,
@@ Line 269: / Line 329: @@
 WHERE TABLE_NAME   = 'table_name'
 order by ordinal_position
+;
+</code>
+==== dba_tab_columns ====
+inefficient but who cares
+<code sql>
+SELECT * from
+(
+SELECT table_catalog DB_NAME,
+    table_schema SCHEMA_NAME,
+    table_name,
+    column_name,
+    ordinal_position,
+    data_type,
+    character_maximum_length,
+    column_default,
+    is_nullable
+FROM information_schema.columns
+order by ordinal_position
+) as dba_tab_columns
+WHERE COLUMN_NAME LIKE 'concession'
 ;
 </code>
@@ Line 282: / Line 363: @@
 FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'your_schema';
+</code>
+===== List column index =====
+==== dba_ind_columns ====
+<code sql>
+SELECT
+    np.nspname AS schema_name,
+    t.relname AS table_name,
+    a.attname AS column_name,
+    i.relname AS index_name
+FROM
+    pg_class t,
+    pg_class i,
+    pg_index ix,
+    pg_attribute a,
+    pg_namespace np
+WHERE
+    t.oid = ix.indrelid
+    AND i.oid = ix.indexrelid
+    AND a.attrelid = t.oid
+    AND a.attnum = ANY(ix.indkey)
+    AND np.oid = t.relnamespace
+    AND t.relkind = 'r'
+    AND a.attname like '%COLUMN_NAME%'
+    AND i.relname like '%INDEX_NAME%'
+    AND t.relname like '%TABLE_NAME%'
+ORDER BY
+    t.relname,
+    a.attname;
 </code>
@@ Line 300: / Line 414: @@
 ===== show object source =====
 Change ''FUNCTION_NAME'':
 <code sql>
 select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
@@ Line 475: / Line 591: @@
 ===== Locks =====
+This query will list all locks:
 <code sql>
 SELECT psa.datid,
@@ Line 490: / Line 607: @@
 ;
 </code>
+This query will list all //blocking sessions//, see [[https://wiki.postgresql.org/wiki/Lock_Monitoring|official documentation]]:
+<code sql>
+SET application_name='%your_logical_name%';
+SELECT blocked_locks.pid     AS blocked_pid,
+         blocked_activity.usename  AS blocked_user,
+         blocking_locks.pid     AS blocking_pid,
+         blocking_activity.usename AS blocking_user,
+         blocked_activity.query    AS blocked_statement,
+         blocking_activity.query   AS current_statement_in_blocking_process,
+         blocked_activity.application_name AS blocked_application,
+         blocking_activity.application_name AS blocking_application
+   FROM  pg_catalog.pg_locks         blocked_locks
+    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
+    JOIN pg_catalog.pg_locks         blocking_locks
+        ON blocking_locks.locktype = blocked_locks.locktype
+        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
+        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
+        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
+        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
+        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
+        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
+        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
+        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
+        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
+        AND blocking_locks.pid != blocked_locks.pid
+    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
+   WHERE NOT blocked_locks.GRANTED;
+</code>
 ===== Change user password =====
 <code sql>
@@ Line 495: / Line 643: @@
 </code>
+===== dump/export all users with password =====
+<code bash>
+pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1
+</code>
 ===== List extensions installed =====
@@ Line 514: / Line 667: @@
+===== List Foreign keys (FK) =====
+From [[https://dataedo.com/kb/query/postgresql/list-foreign-keys|this page]]:
+<code sql>
+select kcu.table_schema || '.' ||kcu.table_name as foreign_table,
+       '>-' as rel,
+       rel_tco.table_schema || '.' || rel_tco.table_name as primary_table,
+       string_agg(kcu.column_name, ', ') as fk_columns,
+       kcu.constraint_name
+from information_schema.table_constraints tco
+join information_schema.key_column_usage kcu
+          on tco.constraint_schema = kcu.constraint_schema
+          and tco.constraint_name = kcu.constraint_name
+join information_schema.referential_constraints rco
+          on tco.constraint_schema = rco.constraint_schema
+          and tco.constraint_name = rco.constraint_name
+join information_schema.table_constraints rel_tco
+          on rco.unique_constraint_schema = rel_tco.constraint_schema
+          and rco.unique_constraint_name = rel_tco.constraint_name
+where tco.constraint_type = 'FOREIGN KEY'
+group by kcu.table_schema,
+         kcu.table_name,
+         rel_tco.table_name,
+         rel_tco.table_schema,
+         kcu.constraint_name
+order by kcu.table_schema,
+         kcu.table_name;
+</code>
 ====== Section: VACUUM ======
@@ Line 652: / Line 835: @@
 <code sql>
 select * from pgagent.pga_job ;
+</code>
+Less info:
+<code sql>
+select jobid,
+    jobname,
+    jobdesc,
+    jobenabled
+from pgagent.pga_job ;
 </code>