Re: Add a check an a array column

[Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>]

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.hoffman@xxxxxxxxx> 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]);