Add a check an a array column

vdg <vdg.encelade@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�

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"
# insert into i values (ARRAY[0,1,2,3,1024]);
ERROR: new row for relation "i" violates check constraint "i_i_check"

Joel

On Sat, Sep 8, 2012 at 8:31 AM, Andreas Kretschmer <
akretschmer@spamfence.net> wrote:

Joel Hoffman <joel.hoffman@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()

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�

On Sat, 2012-09-08 at 21:24 +0200, Andreas Kretschmer wrote:

Joel Hoffman <joel.hoffman@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.

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

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 checked there.

As a wise man once said: your application won't be the last one to use the data and it won't be the only one.
And most of the data that is of any interest lives a lot longer than the application(s).

Nowaday it's not uncommon that multiple applications read and write to the same database and in that case you simply
cannot rely on all of them to validate the data.

Thomas

Thomas Kellerer <spam_eater@gmx.net> 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 declarative constraints *should* be checked there.

As a wise man once said: your application won't be the last one to use the data and it won't be the only one.
And most of the data that is of any interest lives a lot longer than the application(s).

Nowaday it's not uncommon that multiple applications read and write to the same database and in that case you simply
cannot rely on all of them to validate the data.

Full ack.

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�

vdg <vdg.encelade@gmail.com> 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#FUNCTIONS-SUBQUERY-ALL
http://www.postgresql.org/docs/9.1/static/functions-comparisons.html#AEN17447

regards, tom lane

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