Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:postgresql:postgresql_basic_querys

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Next revisionBoth sides next revision
dba:postgresql:postgresql_basic_querys [2022/03/22 16:33] – [Sessions] dodgerdba:postgresql:postgresql_basic_querys [2023/10/09 09:11] – [list functions] dodger
Line 57: Line 57:
  
 ===== autocommit ===== ===== autocommit =====
-<code sql> +*WARNING* ''\set '' has to be on lowercase: \\ 
-\set AUTOCOMMIT off +\\ 
-</code>+''\set AUTOCOMMIT off'' 
  
 Check: Check:
Line 108: Line 109:
  
 ===== list schemas ===== ===== list schemas =====
 +Sortcode:
 <code> <code>
 \dn \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> </code>
  
Line 150: Line 161:
  
 ===== list functions ===== ===== 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> <code sql>
 \da \da
Line 269: Line 327:
 WHERE TABLE_NAME   = 'table_name' WHERE TABLE_NAME   = 'table_name'
 order by ordinal_position 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> </code>
Line 282: Line 361:
 FROM INFORMATION_SCHEMA.STATISTICS FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'your_schema'; 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> </code>
  
Line 514: Line 626:
  
  
 +
 +===== 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 ====== ====== Section: VACUUM ======
  
Line 652: Line 794:
 <code sql> <code sql>
 select * from pgagent.pga_job ; select * from pgagent.pga_job ;
 +</code>
 +
 +Less info:
 +<code sql>
 +select jobid,
 +    jobname,
 +    jobdesc,
 +    jobenabled
 +from pgagent.pga_job ;
 </code> </code>
  
dba/postgresql/postgresql_basic_querys.txt · Last modified: 2024/04/19 07:37 by dodger