ALTERing a CHECK constraint in a DOMAIN

Question

Is there a way to modify the details of an existing DOMAIN constraint in Postgres 13.4?

I've been trying, and checking the docs, and suspect that the answer is: "No. DROP the constraint (or domain and constraint?), and rebuild it."

This is awkward as I've got fields and functions that already use the constraint, so there's a bit of a cascade of items I'd have to also DROP and CREATE. I can do that, but it's a bit involved.

As an example, I've got a simple domain list like this:

DROP DOMAIN IF EXISTS domains.user_name;

CREATE DOMAIN domains.user_name AS
    citext
    NOT NULL
    CONSTRAINT user_name_legal_values
        CHECK(
            VALUE IN (
                'postgres',
                'dbadmin',
                'user_bender',
                'user_cleanup',
                'user_domo_pull'
            )
     );

COMMENT ON DOMAIN domains.user_name IS
    'Valid user_name role names.';

I'd like to change the VALUE IN list in the CHECK by inserting one more name: 'user_analytics'.

Is this possible without dropping and rebuilding the domain, constraint, or both?

If it isn't, I can do the cascaded delete and rebuild, and figure for the future that DOMAIN isn't the right tool for this kind of thing. I can always use a tiny lookup table instead. I just like DOMAIN as it makes parameter and column intentions clearer.

score 1 · Accepted Answer · answered Nov 07 '21 at 05:52

Use ALTER DOMAIN. Drop the old constraint and add a new one. You can't do both in a single command (unlike ALTER TABLE):

ALTER DOMAIN user_name DROP CONSTRAINT user_name_legal_values;
ALTER DOMAIN user_name  ADD CONSTRAINT user_name_legal_values CHECK(
            VALUE IN (
                'postgres',
                'dbadmin',
                'user_analytics',
                'user_bender',
                'user_cleanup',
                'user_domo_pull'
            ));

The manual:

ADD domain_constraint [ NOT VALID ]

This form adds a new constraint to a domain using the same syntax as CREATE DOMAIN. When a new constraint is added to a domain, all columns using that domain will be checked against the newly added constraint. [...]

Since you just allow an additional value, no existing column can conflict.

ALTERing a CHECK constraint in a DOMAIN

1 Answers1