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, 08 September 2012 13:18:25 Bret Stern wrote:
On Sat, 2012-09-08 at 21:24 +0200, Andreas Kretschmer wrote:
Joel Hoffman <joel.hoff...@gmail.com> 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" violates check constraint "i_i_check"
Nice! Didn't know that with all()
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.
Andreas
I find rewriting examples a good way of understanding things, and as I
was not sure about the use of 'i' as both table name and field name I
rewrote the example given. I also gave it slightly more realistic names
and added a few extra fields. The rewritten example performs exactly as
the original for the purposes of the question.
My custom is to write SQL as a script and ten execute it, this allows me
to keep the example for later use, and to correct any mistakes I make.
I made no change in the _syntax_ of the check condition.
I hope this helps.
Cheers,
Gavin
DROP TABLE IF EXISTS tarcon;
CREATE TABLE tarcon
(
id serial PRIMARY KEY,
name text,
va int[] check (0 <= ALL(va) AND 1023 >= ALL(va)),
ok boolean
);
-- succeeds
INSERT INTO tarcon (va)
VALUES (ARRAY[0,1,2,3,1023]);
-- gives ERROR
INSERT INTO tarcon (va)
VALUES (ARRAY[0,1,2,3,-1]);
