vdg <vdg.encel...@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) 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° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
