Re: [GENERAL] Add a check an a array column

2012-09-09 Thread Gavin Flower
On 09/09/12 23:12, vdg wrote: Thanks for your help. Before posting, I had tried something like check ((ALL(i) >= 0) AND (ALL(i) <= 1024 ))); but i got syntax errors. It seems the first ALL() was not recognized. Could someone give me documentation hints on this behaviour ? vdg On Saturday,

Re: [GENERAL] Add a check an a array column

2012-09-09 Thread Tom Lane
vdg writes: > Before posting, I had tried something like > check ((ALL(i) >= 0) AND (ALL(i) <= 1024 ))); > but i got syntax errors. Well, that's not the syntax. > Could someone give me documentation hints on this behaviour ? http://www.postgresql.org/docs/9.1/static/functions-subquery.html#F

Re: [GENERAL] Add a check an a array column

2012-09-09 Thread Andreas Kretschmer
Thomas Kellerer wrote: > Bret Stern wrote on 08.09.2012 22:18: >> A better place for validation is in the front-end, before >> adding/attempting to add data to the db (my opinion). >> Nice to see there are always other ways though. >> > > I beg to differ: every validation that can be enforced by

Re: [GENERAL] Add a check an a array column

2012-09-09 Thread Thomas Kellerer
Bret Stern wrote on 08.09.2012 22:18: A better place for validation is in the front-end, before adding/attempting to add data to the db (my opinion). Nice to see there are always other ways though. I beg to differ: every validation that can be enforced by declarative constraints *should* be c

Re: [GENERAL] Add a check an a array column

2012-09-09 Thread vdg
Thanks for your help. Before posting, I had tried something like check ((ALL(i) >= 0) AND (ALL(i) <= 1024 ))); but i got syntax errors. It seems the first ALL() was not recognized. Could someone give me documentation hints on this behaviour ? vdg On Saturday, 08 September 2012 13:18:25 Bret

Re: [GENERAL] Add a check an a array column

2012-09-08 Thread Bret Stern
On Sat, 2012-09-08 at 21:24 +0200, Andreas Kretschmer wrote: > Joel Hoffman wrote: > > > 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]); > > # in

Re: [GENERAL] Add a check an a array column

2012-09-08 Thread Andreas Kretschmer
Joel Hoffman wrote: > 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"

Re: [GENERAL] Add a check an a array column

2012-09-08 Thread Joel Hoffman
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_che

Re: [GENERAL] Add a check an a array column

2012-09-08 Thread Andreas Kretschmer
vdg 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) > > bu

[GENERAL] Add a check an a array column

2012-09-08 Thread vdg
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 th