Re: Add a check an a array column

vdg <vdg.encelade@gmail.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)
fromunnest($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°