Postgres CHECK() expression in column with array-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Actually a comment, but I wanted to be able to share this with proper formatting:

It looked like you're trying to dump month lengths to an array of integers. To generate an array of numbers of days in each month for a given year:

select  array_agg(
            extract(
                'days' from (
                    date_trunc('years', now()) --returns beginning of this year
                      (month_offset||' months - 1 day')::interval
                )
            )
        ) as month_lengths
from generate_series(1,12,1) a(month_offset);

--             month_lengths
-----------------------------------------
-- {31,28,31,30,31,30,31,31,30,31,30,31}

There's usually a way to stick to date/time type. If you present where and why you hold years, months, days, times, intervals, date ranges, durations as integers, and what your validation logic needs to be, it might be possible to optimise it so that you can operate on relevant date/time types directly with a bit more ease, using their native built-in functions, without having to set up int-based equivalents.