Postgres constraints on Composite Types-CodePudding

Ideally I'd like to create checks on composite types in order to validate that wherever this composite type is used that at least one of the fields inside is non-null

I've got the following code for setting up my type and table

CREATE TYPE public.foo AS (
    min real,
    max real,
    other real,
    extra bool
);


create table public.bar (
    a foo not null check (num_nonnulls((a).min, (a).max, (a).other) > 0)
    b foo not null,
    c foo not null
);

In the above I would like to check that at least one of the min, max, or other is non-null wherever the foo type is used. The only way I've been able to achieve this so far is by adding the check on the column definition as seen above, which is quite cumbersome since I have to do it everywhere the composite type is used, and worst of all it can be forgotten to be added. But it does work..

Additionally I had considered using a domain instead here given their support for checks on the type, however, they unfortunately from my understanding don't support composite types.

Is there another easy way around this that I'm missing?

CodePudding user response：

Domains can use custom types:

create domain foo_domain as foo
   not null
   constraint check_num_nulls 
     check (num_nonnulls ((value)."min", (value)."max", (value).other) > 0);

create table bar (
    a foo_domain,
    b foo_domain,
    c foo_domain
);