Query for the allowed values in a constraint

Question

I want to extract values from a CHECK constraint on a database table. The code is:

CONSTRAINT Shop_check_serviceType CHECK (service_type IN ('food or drink', 'entertainment', 'retail'))

In Postgres 12 there is a column named consrc in pg_catalog.pg_constraint. But I use Postgres 14 and I don't know how to extract the values there. I have tried to search the manual without success.

Answer 1

Use the dedicated function pg_get_constraintdef() to reverse-engineer the SQL-DDL code of constraint definition.

SELECT pg_get_constraintdef(oid)
FROM   pg_catalog.pg_constraint
WHERE  contype  = 'c'                          -- CHECK constraint
AND    conrelid = 'public.my_table'::regclass  -- your table name here
AND    connname = 'shop_check_servicetype';    -- lower-cased?

If you did not double-quote the constraint name "Shop_check_serviceType" it has been converted to lower-case.

Related:

• Delete rows with foreign key in PostgreSQL

BTW, the (redundant) column consrc existed up to Postgres 11 and had already been dropped from pg_catalog.pg_constraint in Postgres 12. pg_get_constraintdef() reproduces what used to be in that column.

Quoting the release notes of Postgres 12:

• Remove obsolete pg_constraint.consrc column (Peter Eisentraut)

  This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a check constraint's expression from pg_constraint is pg_get_expr(conbin, conrelid). pg_get_constraintdef() is also a useful alternative.