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.

Answer 1

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.

Answer 2

I needed to alter this domain today, did a search....and hit this question from a few years back. Below is an updated example with a few notes. Might help someone else, or might help me Future Me in another few years...

ALTER DOMAIN domains.user_name
        DROP CONSTRAINT IF EXISTS user_name_legal_values; -- Clear the old definition.

ALTER DOMAIN domains.user_name 
         ADD CONSTRAINT user_name_legal_values-- Set the updated definition
                 CHECK(
                        VALUE IN (
                            'rds_super',
                            'rdsadmin',
                            'user_admin',
                            'user_analytics',
                            'user_backup',
                            'user_bender',
                            'user_change_structure',
                            'user_cleanup',
                            'user_dba_task',
                            'user_domo_pull',
                            'user_duck',
                            'user_iceberg_remote',
                            'user_iceberg',
                            'user_leviathan',
                            'user_listener',
                            'user_push',
                            'user_quick',
                            'user_reporting',
                            'user_rest',
                            'user_saws',
                            'user_sonar')
                ) NOT VALID; 
/* 
                  NOT VALID goes down here, after the constraint definition.
          
NOT VALID requires a heavy lock, which *may* lead to a wait: 
https://dba.stackexchange.com/questions/268301/why-is-add-constraint-not-valid-taking-a-long-time
As of PG 16.1, NOT VALID is needed here to avoid an automatic error from PG, based 
on a field dependency check.
*/


-- Shouldn't need to validate the rule now, as Erwin pointed out originally.
-- Still, here's the syntax, and the outcome that I get:
ALTER DOMAIN domains.user_name
     VALIDATE CONSTRAINT user_name_legal_values; -- If run, rechecks *entire* table.

-- ERROR:  cannot alter type "user_name" because column "test_case.run_as" uses it. 0.000 seconds. (Line 43).