Thread: Add a check an a array column

Add a check an a array column

From

vdg

Date:

08 September 2012, 17:27:53

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



Any help ?

Thank you

vdg

Re: Add a check an a array column

From

Andreas Kretschmer

Date:

08 September 2012, 18:31:44

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)
fromunnest($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°

Re: Add a check an a array column

From

Joel Hoffman

Date:

08 September 2012, 19:29:59

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:

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"

Re: Add a check an a array column

From

Andreas Kretschmer

Date:

08 September 2012, 22:24:19

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°

Re: Add a check an a array column

From

Bret Stern

Date:

08 September 2012, 23:26:31

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
> --
> 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°
>
>

Re: Add a check an a array column

From

vdg

Date:

09 September 2012, 14:12:39

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

Re: Add a check an a array column

From

Thomas Kellerer

Date:

09 September 2012, 14:36:54

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

Re: Add a check an a array column

From

Andreas Kretschmer

Date:

09 September 2012, 15:01:11

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°

Re: Add a check an a array column

From

Tom Lane

Date:

09 September 2012, 18:24:19

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

Re: Add a check an a array column

From

Gavin Flower

Date:

09 September 2012, 21:43:34

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