Basically I want to put a CHECK for all items of an array to be positive, something like this:
CREATE TABLE mField(
fields int[] CHECK( items_in_array(>0))
);
so that all items in the fields are positive only. Is there a way to do this in postgres?
This can be done with 0 < ALL:
create table mfield (
fields int[] check(0 < ALL (fields))
);
db<>fiddle here
There is nothing built-in, but it's easy to write a function for that:
create function all_positive(p_input int[])
returns boolean
as
$$
select count(*) = 0
from unnest(p_input) as x(val)
where x.val <= 0;
$$
language sql
immutable;
create table my_table
(
fields int[] check(all_positive(fields))
);