Re: Add a check an a array column

vdg <vdg(dot)encelade(at)gmail(dot)com> wrote:

> Hello,
>
> I have a column defined as
>
> test bigint[]
>
> I would like to add a constraint on this column: the values stored must be
> between 0 and 1023 inclusive
>
> I know how to add a constraint on a column which is not an array:
>
> check (test < x'400'::bigint)
>
> but i can't find the way to do that when there is an array of values

Why bigint for values between 0 and 1023?

Okay, i think something like this:

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"

(only for values greater than 10, but i think you can see the wa...)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°