Skip to content

Instantly share code, notes, and snippets.

@viveksinha

viveksinha/primary_key_postgresql.sql

Forked from mmasashi/example_query.txt
Last active May 14, 2019 06:06
Show Gist options
  • Save viveksinha/3e76177d63cd225b056221357aad141a to your computer and use it in GitHub Desktop.
Save viveksinha/3e76177d63cd225b056221357aad141a to your computer and use it in GitHub Desktop.
Download ZIP
Get primary key columns on PostgreSQL/Amazon Redshift
Raw
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;
Raw
redshift-postgres_primary_key_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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment