Thread: Can I use a constraint to make sure all array elements are positive?

One of the columns in my table is FLOAT[] NOT NULL. Can I use a constraint to
make sure all array elements are positive?



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Can-I-use-a-constraint-to-make-sure-all-array-elements-are-positive-tp5796846.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On 03/19/2014 10:59 PM, AlexK wrote:
> One of the columns in my table is FLOAT[] NOT NULL. Can I use a constraint to
> make sure all array elements are positive?

You can make a function that checks an array, and then use that in a
CHECK constraint.

Also, note that  select array[null]::float[] is null;  returns false. 
That may not be what you want.

-- 
Vik

On Wed, Mar 19, 2014 at 2:59 PM, AlexK <alkuzo@gmail.com> wrote:
> One of the columns in my table is FLOAT[] NOT NULL. Can I use a constraint to
> make sure all array elements are positive?

Sure, you can. Use all() in the CHECK constraint:

CREATE TABLE c (f float[] NOT NULL CHECK (0 < all(f)));
CREATE TABLE

INSERT INTO c VALUES (array[1,2,3]);
INSERT 0 1

INSERT INTO c VALUES (array[1,2,3,0]);
ERROR:  new row for relation "c" violates check constraint "c_f_check"
DETAIL:  Failing row contains ({1,2,3,0}).

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

On 03/19/2014 11:20 PM, Sergey Konoplev wrote:
> On Wed, Mar 19, 2014 at 2:59 PM, AlexK <alkuzo@gmail.com> wrote:
>> One of the columns in my table is FLOAT[] NOT NULL. Can I use a constraint to
>> make sure all array elements are positive?
> Sure, you can. Use all() in the CHECK constraint:
>
> CREATE TABLE c (f float[] NOT NULL CHECK (0 < all(f)));
> CREATE TABLE
>
> INSERT INTO c VALUES (array[1,2,3]);
> INSERT 0 1
>
> INSERT INTO c VALUES (array[1,2,3,0]);
> ERROR:  new row for relation "c" violates check constraint "c_f_check"
> DETAIL:  Failing row contains ({1,2,3,0}).

D'oh!

Much better than my solution.

-- 
Vik