Search code examples
sqlpostgresqlconstraintsalter

ADD Constraint to ALTER DOMAIN ERROR

When I execute: 

ALTER DOMAIN public.remainingamount
    ADD CONSTRAINT "chk_remainingAmount" CHECK (remainingAmount > 0);

I recevied the following message in pgAdmin 4:

ERROR: column "remainingamount" does not exist SQL state: 42703

The column exists. I can see it in de sidebar! As evidence, when I execute te following line:

ALTER DOMAIN public.remainingamount
    ADD CONSTRAINT "chk_remainingAmount" CHECK (remainingAmount > 0);

I receive the message:

ERROR: type "remainingamount" already exists SQL state: 42710

How can I add a new constraint to an existing domain?

Solution

  • The only variable you can use in CHECK constraint of a DOMAIN is VALUE. Assuming that the base type is money:

    ALTER DOMAIN public.remainingamount
    ADD CONSTRAINT "chk_remainingAmount" CHECK (VALUE > 0::money);

    Per the documentation:

    CHECK (expression)

    (...)

    Currently, CHECK expressions cannot contain subqueries nor refer to variables other than VALUE.