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
dba:postgresql:postgresql_basic_querys [2022/03/22 16:33] – [Sessions] dodgerdba:postgresql:postgresql_basic_querys [2024/04/19 07:37] (current) – [Section: EMPTY] 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 177: Line 235:
  
 ===== list objects in schema ===== ===== list objects in schema =====
-Change ''<SCHEMA_NAME>'':+Change ''<SCHEMA_NAME>'' : 
 + 
 <code sql> <code sql>
 select n.nspname as schema_name, select n.nspname as schema_name,
Line 269: Line 329:
 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 363:
 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 300: Line 414:
 ===== show object source ===== ===== show object source =====
 Change ''FUNCTION_NAME'': Change ''FUNCTION_NAME'':
 +
 +
 <code sql> <code sql>
 select prosrc from pg_proc where proname='FUNCTION_NAME'  ; select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
Line 475: Line 591:
  
 ===== Locks ===== ===== Locks =====
 +This query will list all locks:
 <code sql> <code sql>
 SELECT psa.datid, SELECT psa.datid,
Line 490: Line 607:
 ; ;
 </code> </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 ===== ===== Change user password =====
 <code sql> <code sql>
Line 495: Line 643:
 </code> </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 ===== ===== 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 ====== ====== Section: VACUUM ======
  
Line 652: Line 835:
 <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>
  
Line 693: Line 885:
  
  
-====== Section: EMPTY ======+====== Section: XML (tables) ====== 
 +This is an example on how to use XMLTABLE function to extract data from a XML column: 
 +<code sql> 
 +    select 
 +        bdef.build_definition_id as build_definition_id, 
 +        tasks.task_definition_id as task_definition_id, 
 +        tasks.user_description as user_description, 
 +        tasks.is_enabled as is_enabled, 
 +        tasks.plugin_key as plugin_key, 
 +        tasks.finalising as finalising, 
 +        config_items.item_key as item_key, 
 +        config_items.item_value as item_value 
 +    FROM 
 +        build_definition bdef, 
 +        XMLTABLE('//taskDefinition' PASSING XMLPARSE(DOCUMENT xml_definition_data) 
 +                  COLUMNS 
 +                      task_definition_id integer PATH 'id', 
 +                      user_description text PATH 'userDescription', 
 +                      is_enabled boolean PATH 'isEnabled', 
 +                      plugin_key text PATH 'pluginKey', 
 +                      finalising boolean PATH 'finalising', 
 +                      config XML PATH 'config') AS tasks 
 +    left join XMLTABLE('//item' PASSING tasks.config COLUMNS item_key text PATH 'key',item_value text PATH 'value') AS config_items 
 +    on true 
 +    where xml_definition_data LIKE '%\/dip\/%' 
 +    order by 1 
 +; 
 +</code>
  
  
  
  
 +====== Section: EMPTY ======
  
  
- +<code sql>
- +
- +
-<code>+
 </code> </code>
  
dba/postgresql/postgresql_basic_querys.1647966797.txt.gz · Last modified: 2022/03/22 16:33 by dodger