wasabigeek

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.

Comments


Written by Nick who is a indie-hacker wannabe, living in sunny Singapore. Twitter | GitHub