Re: Add a check an a array column

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(dot)hoffman(at)gmail(dot)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]);