2

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?

Joerg
  • 790
  • 2
  • 10
  • 23

1 Answers1

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • 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? – Joerg Jun 01 '22 at 09:44