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:
SELECT con.* 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>';