dba:postgresql:postgresql_basic_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
dba:postgresql:postgresql_basic_querys [2023/09/20 10:08] – [List column index] dodger | dba:postgresql:postgresql_basic_querys [2024/04/19 07:37] (current) – [Section: EMPTY] dodger | ||
---|---|---|---|
Line 161: | Line 161: | ||
===== list functions ===== | ===== list functions ===== | ||
+ | |||
+ | <code sql> | ||
+ | \df | ||
+ | </ | ||
+ | |||
+ | Or | ||
+ | <code sql> | ||
+ | SELECT n.nspname as " | ||
+ | p.proname as " | ||
+ | CASE p.prokind | ||
+ | WHEN ' | ||
+ | WHEN ' | ||
+ | WHEN ' | ||
+ | ELSE ' | ||
+ | END as " | ||
+ | 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 <> ' | ||
+ | AND n.nspname <> ' | ||
+ | ORDER BY 1, 2; | ||
+ | </ | ||
+ | |||
+ | |||
+ | or complete: | ||
+ | <code sql> | ||
+ | SELECT n.nspname as " | ||
+ | p.proname as " | ||
+ | pg_catalog.pg_get_function_result(p.oid) as " | ||
+ | pg_catalog.pg_get_function_arguments(p.oid) as " | ||
+ | CASE p.prokind | ||
+ | WHEN ' | ||
+ | WHEN ' | ||
+ | WHEN ' | ||
+ | ELSE ' | ||
+ | END as " | ||
+ | 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 <> ' | ||
+ | AND n.nspname <> ' | ||
+ | ORDER BY 1, 2; | ||
+ | </ | ||
+ | Note: | ||
+ | " | ||
+ | |||
+ | ==== Aggregate functions ==== | ||
+ | |||
<code sql> | <code sql> | ||
\da | \da | ||
Line 188: | Line 235: | ||
===== list objects in schema ===== | ===== list objects in schema ===== | ||
- | Change ''< | + | Change ''< |
+ | |||
<code sql> | <code sql> | ||
select n.nspname as schema_name, | select n.nspname as schema_name, | ||
Line 280: | Line 329: | ||
WHERE TABLE_NAME | WHERE TABLE_NAME | ||
order by ordinal_position | order by ordinal_position | ||
+ | ; | ||
+ | </ | ||
+ | ==== 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 ' | ||
; | ; | ||
</ | </ | ||
Line 298: | Line 368: | ||
===== List column index ===== | ===== List column index ===== | ||
- | aka `dba_ind_columns`: | + | ==== dba_ind_columns |
<code sql> | <code sql> | ||
SELECT | SELECT | ||
- | t.relname AS TABLE_NAME, | + | |
+ | | ||
a.attname AS column_name, | a.attname AS column_name, | ||
- | i.relname AS index_name, | + | i.relname AS index_name |
FROM | FROM | ||
pg_class t, | pg_class t, | ||
pg_class i, | pg_class i, | ||
pg_index ix, | pg_index ix, | ||
- | pg_attribute a | + | pg_attribute a, |
+ | pg_namespace np | ||
WHERE | WHERE | ||
t.oid = ix.indrelid | t.oid = ix.indrelid | ||
Line 315: | Line 388: | ||
AND a.attrelid = t.oid | AND a.attrelid = t.oid | ||
AND a.attnum = ANY(ix.indkey) | AND a.attnum = ANY(ix.indkey) | ||
+ | AND np.oid = t.relnamespace | ||
AND t.relkind = ' | AND t.relkind = ' | ||
- | AND t.relname | + | |
+ | AND i.relname like ' | ||
+ | | ||
ORDER BY | ORDER BY | ||
t.relname, | t.relname, | ||
a.attname; | a.attname; | ||
- | < | + | </code> |
===== show current schema ===== | ===== show current schema ===== | ||
< | < | ||
Line 337: | Line 414: | ||
===== show object source ===== | ===== show object source ===== | ||
Change '' | Change '' | ||
+ | |||
+ | |||
<code sql> | <code sql> | ||
select prosrc from pg_proc where proname=' | select prosrc from pg_proc where proname=' | ||
Line 512: | Line 591: | ||
===== Locks ===== | ===== Locks ===== | ||
+ | This query will list all locks: | ||
<code sql> | <code sql> | ||
SELECT psa.datid, | SELECT psa.datid, | ||
Line 527: | Line 607: | ||
; | ; | ||
</ | </ | ||
+ | |||
+ | This query will list all //blocking sessions//, see [[https:// | ||
+ | <code sql> | ||
+ | SET application_name=' | ||
+ | SELECT blocked_locks.pid | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | JOIN pg_catalog.pg_stat_activity blocked_activity | ||
+ | JOIN pg_catalog.pg_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; | ||
+ | </ | ||
+ | |||
+ | |||
===== Change user password ===== | ===== Change user password ===== | ||
<code sql> | <code sql> | ||
Line 532: | Line 643: | ||
</ | </ | ||
+ | |||
+ | ===== dump/export all users with password ===== | ||
+ | <code bash> | ||
+ | pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1 | ||
+ | </ | ||
===== List extensions installed ===== | ===== List extensions installed ===== | ||
Line 769: | Line 885: | ||
- | ====== Section: | + | ====== Section: |
+ | 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('// | ||
+ | COLUMNS | ||
+ | task_definition_id integer PATH ' | ||
+ | user_description text PATH ' | ||
+ | is_enabled boolean PATH ' | ||
+ | plugin_key text PATH ' | ||
+ | finalising boolean PATH ' | ||
+ | config XML PATH ' | ||
+ | left join XMLTABLE('// | ||
+ | on true | ||
+ | where xml_definition_data LIKE ' | ||
+ | order by 1 | ||
+ | ; | ||
+ | </ | ||
+ | ====== Section: EMPTY ====== | ||
- | + | < | |
- | + | ||
- | + | ||
- | < | + | |
</ | </ | ||
dba/postgresql/postgresql_basic_querys.1695204506.txt.gz · Last modified: 2023/09/20 10:08 by dodger