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.
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.
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
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:
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!
- 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.