postgresqlcheck-constraints

How to define CHECK constraint for a Postgres DOMAIN that is based on a composite type

I would like to define a Postgres domain with check constraints based on a custom composite type.

An example for a naive approach would be this:

CREATE TYPE raw_comp_foo AS (
    min_value    integer,
    max_value    integer
);
CREATE DOMAIN comp_foo AS raw_comp_foo
CHECK (VALUE.min_value < VALUE.max_value);

However, I get the error message missing FROM-clause entry for table "value". How can I achieve the desired constraint in the example above?

Solution

  • Place the composite value in parentheses:

    CREATE DOMAIN comp_foo AS raw_comp_foo
CHECK ((VALUE).min_value < (VALUE).max_value);

    The issue is explained in Accessing Composite Types.