postgres - creating table with array of positive integers-CodePudding

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?

CodePudding user response：

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))
);

CodePudding user response：

This can be done with 0 < ALL:

create table mfield ( 
  fields int[] check(0 < ALL (fields))
);

db<>fiddle here