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

Question

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?

klin · Accepted Answer · 2023-08-30 12:03:45Z

2

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.

edited Aug 30, 2023 at 12:03

answered Jun 1, 2022 at 9:40

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joerg Over a year ago

Oh wow. So this was only a syntax problem after all? And there I was, assuming it was a semantic problem. Could you maybe add a documentation reference stating that VALUE should be put in parentheses?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related