1

The syntax for ALTER DOMAIN IN POSTGRES DDL allows for CHECK condition to be changed provided a named constraint exists.

How should one change a CHECK defined in a domain which does not specify a named constraint

Say the original constraint is as follows

CREATE DOMAIN ref_type_type varchar(5) CHECK (VALUE IN ('00001', '00002', '00003'));

As part of alteration, an additional value '00004' is needed to be added as valid.

Sudheer Hebbale
  • 421
  • 5
  • 11

1 Answers1

0

The check constraint will still have a name - just a generated one. You can retrieve the name using the following query:

select t.typname as domain_name, 
       c.conname as constraint_name
from pg_catalog.pg_type t 
  join pg_catalog.pg_constraint c on t.oid = c.contypid 
where t.typtype = 'd' 
  and t.typnamespace = 'public'::regnamespace --<< or whatever schema you use
  and t.typname = 'ref_type_type'

But you can't really alter the check constraint, you need to drop and re-create it.