Skip to content

Instantly share code, notes, and snippets.

@kthwaite

kthwaite/list_psql_columns.sql

Created June 15, 2018 19:27
Show Gist options
  • Save kthwaite/b6d46217a5165580867c0f537e37b52d to your computer and use it in GitHub Desktop.
Save kthwaite/b6d46217a5165580867c0f537e37b52d to your computer and use it in GitHub Desktop.
Download ZIP
list columns of PostgreSQL tables inc. primary keys and foreign key relationships
Raw
list_psql_columns.sql
SELECT
c.table_catalog,
t.table_schema,
t.table_name,
c.column_name,
c.ordinal_position,
c.data_type,
c.udt_name,
c.character_maximum_length,
c.numeric_precision,
c.numeric_precision_radix,
c.numeric_scale,
pk.is_primary_key,
pk.constraint_name,
fk.constraint_name,
fk.foreign_table_schema,
fk.foreign_table_name,
fk.foreign_column_name
FROM information_schema.columns AS c
JOIN (
SELECT
schemaname AS table_schema,
tablename AS table_name
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
) AS t
USING (table_schema, table_name)
LEFT JOIN (
SELECT
tc.table_schema,
tc.table_name,
kcu.column_name,
tc.constraint_name,
true AS is_primary_key
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
USING (constraint_name)
WHERE constraint_type = 'PRIMARY KEY'
) AS pk
USING (table_schema, table_name, column_name)
LEFT JOIN (
SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
USING (constraint_name)
JOIN information_schema.constraint_column_usage AS ccu
USING (constraint_name)
WHERE constraint_type = 'FOREIGN KEY'
) AS fk
USING (table_schema, table_name, column_name)
ORDER BY t.table_schema, t.table_name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment