postgresqlcheck-constraints

Postgres CHECK() expression in column with array

I can't find how to check each element of an array-field in a table. Be like:

create table tab (
    day_in_mounth int[12] check ( *every array element* > 0 and < 30)
);

values < 0 and > 30 must not be skipped in the table after entering this check.

Solution

  • You can use the ALL operator:

    create table tab (
    day_in_month int[12] check (     0 < all(day_in_month) 
                                and 32 > all(day_in_month) ) 
);

    Note that I used 32 > all() as there are months with 31 days which I guess should be valid as well. If your calendar does not have months with 31 days, use 31 > all (...) to exclude months with more than 30 days.

    This would still allow NULL values as array elements though. If you also want to prevent NULL values, you can add:

    and array_position(day_in_month, null) = 0