开发者

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开发者_开发问答.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜