Skip to content

Instantly share code, notes, and snippets.

@timwis timwis/get-schema.sql
Last active May 4, 2017

Embed
What would you like to do?
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
You can’t perform that action at this time.