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/05/19 15:30] – [autocommit] dodgerdba:postgresql:postgresql_basic_querys [2023/10/09 09:11] – [list functions] dodger
Line 109: 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 151: 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 270: 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 283: 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 515: 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 ======
  
dba/postgresql/postgresql_basic_querys.txt · Last modified: 2024/04/19 07:37 by dodger