Skip to content

Instantly share code, notes, and snippets.

@timwis
Last active May 4, 2017 10:38
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save timwis/2b3c93c59a3626f351f53248b2d3e6bf to your computer and use it in GitHub Desktop.
Get postgres table schema including keys/constraints
SELECT
cols.column_name,
cols.data_type,
cols.character_maximum_length,
cols.column_default,
cols.is_nullable::boolean,
constr.constraint_type,
pg_catalog.col_description(cls.oid, cols.ordinal_position::int)::jsonb
FROM
pg_catalog.pg_class cls,
information_schema.columns cols
LEFT JOIN
information_schema.key_column_usage keys
ON keys.column_name = cols.column_name
AND keys.table_catalog = cols.table_catalog
AND keys.table_schema = cols.table_schema
AND keys.table_name = cols.table_name
LEFT JOIN
information_schema.table_constraints constr
ON constr.constraint_name = keys.constraint_name
WHERE
cols.table_catalog = 'postgres' AND
cols.table_schema = 'public' AND
cols.table_name = table_name_param AND
cols.table_name = cls.relname;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment