Re: Add a check an a array column

More concisely, you can compare directly against all values of the array:

# create table i (i int[] check (0 <= ALL(i) AND 1023 >= ALL(i)));
# insert into i values (ARRAY[0,1,2,3,1023]);
# insert into i values (ARRAY[0,1,2,3,-1]);
ERROR: new row for relation "i" violates check constraint "i_i_check"
# insert into i values (ARRAY[0,1,2,3,1024]);
ERROR: new row for relation "i" violates check constraint "i_i_check"

Joel

On Sat, Sep 8, 2012 at 8:31 AM, Andreas Kretschmer <
akretschmer(at)spamfence(dot)net> wrote:

> test=# create or replace function check_array(int[]) returns bool as
> $declare i int; begin select into i max(unnest) from unnest($1); if i > 10
> then return false; end if; return true; end$ language plpgsql ;
> CREATE FUNCTION
> Time: 0,579 ms
> test=*# create table a (i int[] check (check_array(i)));
> CREATE TABLE
> Time: 6,768 ms
> test=*# insert into a values (array[1,2,3]);
> INSERT 0 1
> Time: 0,605 ms
> test=*# insert into a values (array[1,2,30]);
> ERROR: new row for relation "a" violates check constraint "a_i_check"
>