Helpful PostgreSQL Queries

introspection.sql

-- List all Foreign Keys
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;
         
         
-- Exact row counts
-- Source: https://stackoverflow.com/a/38684225
select table_schema,
       table_name,
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
       select table_name, table_schema,
              query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
       from information_schema.tables
       where table_schema = 'public' --<< change here for the schema you want
     ) t;
     
-- Get table page counts and sizes
SELECT relname, nspname, pg_size_pretty(pg_relation_size('"' || relname || '"')), c.relpages
  FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','')
   AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
   AND pg_table_is_visible(c.oid)
 ORDER BY relname;

monitoring.sql

-- View all active connections
SELECT * FROM pg_stat_activity;

performance_metrics.sql

-- from http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/
-- Percent of time and index is used on tables
-- can add WHERE n_live_tup > <some value>
SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 
ORDER BY 
  n_live_tup DESC;
  
-- Percent of reads from cache
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;
                                                 
-- Percent of indexes within cache
SELECT 
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM 
  pg_statio_user_indexes;                                                 
                                                 
                                                 

string_manipulation.md

Convert empty strings and various whitespace to NULL

SELECT NULLIF(REGEXP_REPLACE(null, '\s', '', 'g'), ''),          -- null
       NULLIF(REGEXP_REPLACE('', '\s', '', 'g'), ''),            -- empty string
       NULLIF(REGEXP_REPLACE(E'    \n\n\t', '\s', '', 'g'), ''), -- spaces tabs newline string
       NULLIF(REGEXP_REPLACE('stuff', '\s', '', 'g'), '');       -- has character data, will not be null

time.sql

-- list all time zones
SELECT name, abbrev, utc_offset, is_dst
FROM pg_timezone_names;

-- show the time zone
show timezone;

validation.sql

-- Get MD5 of table data. Can be used for a quick and dirty comparison.
SELECT MD5(CAST((ARRAY_AGG(t.* ORDER BY id)) AS text))
FROM <table_name> t;