Get primary key columns on PostgreSQL/Amazon Redshift

example_query.txt

-- Get primary keys for "fsb_schema_7_7379bca4"."m_test_table_multi_pk"

SELECT
    f.attnum AS number,
    c.relname AS table_name,
    f.attname AS column_name
FROM pg_attribute f
    JOIN pg_class c ON c.oid = f.attrelid
    JOIN pg_type t ON t.oid = f.atttypid
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
    AND n.nspname = 'fsb_schema_7_7379bca4'    -- schema name
    AND c.relname = 'm_test_table_multi_pk' -- table name
    AND p.contype = 'p'  -- primary key
    AND f.attnum > 0
ORDER BY f.attnum;

-- Reuslt
 number |      table_name       | column_name
--------+-----------------------+-------------
      1 | m_test_table_multi_pk | id1
      2 | m_test_table_multi_pk | id2
(2 rows)

primary_key_postgresql.sql

SELECT
    f.attnum AS number,
    c.relname AS table_name,
    f.attname AS column_name,
FROM pg_attribute f
    JOIN pg_class c ON c.oid = f.attrelid
    JOIN pg_type t ON t.oid = f.atttypid
    LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
    LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
    AND n.nspname = $1    -- schema name
    AND c.relname = $2 -- table name
    AND p.contype = 'p'  -- primary key
    AND f.attnum > 0
ORDER BY number;