I recently needed to update the check constraint on a Postgres table column. This check does format validation similar to regex. This check was part of the CREATE TABLE statement meaning it was created with the table.
However, ALTER COLUMN is not supporting updates of checks. So what we need to do is to drop the constraint and create a new one.
dropConstraint requires the name of the constraint, but the name is not obvious. This is because
check was create with
createTable. To find the name, there’s a SQL query we can run:
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel
ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp
ON nsp.oid = connamespace
WHERE nsp.nspname = '<schema name>'
AND rel.relname = '<table name>';