Meta-Postgres: What constraints does a table have?
May 16, 2021
TIL that checking the structure of your database is just another query away! I needed to find whether a CHECK constraint existed and learnt that Postgres has information_schema views and system catalogs which you can query like regular tables.
The information_schema is actually defined by the SQL standard, so other SQL-compliant databases should have them too. I tried looking first at columns, then constraint_column_usage. Neither had quite what I was looking for, as they did not include the constraint definition.
Then I found out that each information_schema is actually just a view based upon Postgres’ system catalog tables 🤯 For example, check out the SQL for constraint_column_usage.
This meant I had the building blocks to make exactly what I needed! The pg_node_tree column contains a representation of the CHECK definition, which can be parsed with pg_get_constraintdef:
SELECT pg_get_constraintdef((
SELECT c.oid
FROM pg_constraint c
WHERE c.conrelid = 'my_table'::regclass
));(Note to self: the additional parentheses is to evaluate the select result as a value expression / scalar, as opposed to a table.)
That would give you a result like:
| pg_get_constraintdef |
|---|
CHECK ((description <> ''::text)) |
Appendix
Pro-Tips
Twitter user ascherbaum has pointed out that when using psql, you can use -e -E arguments to show the underlying SQL which Postgres uses. Pretty neat!
Follow-up Questions
- Are the catalogs stored like any other table, or is there some magic that makes them table-like?
- Does pg_get_constraintdef work with other types of constraints? The docs suggest not.