agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedCan I use a constraint to make sure all array elements are positive?
4+ messages / 3 participants
[nested] [flat]
* Can I use a constraint to make sure all array elements are positive?
@ 2014-03-19 21:59 AlexK <alkuzo@gmail.com>
2014-03-19 22:08 ` Re: Can I use a constraint to make sure all array elements are positive? Vik Fearing <vik.fearing@dalibo.com>
2014-03-19 22:20 ` Re: Can I use a constraint to make sure all array elements are positive? Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: AlexK @ 2014-03-19 21:59 UTC (permalink / raw)
To: pgsql-sql
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-p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can I use a constraint to make sure all array elements are positive?
2014-03-19 21:59 Can I use a constraint to make sure all array elements are positive? AlexK <alkuzo@gmail.com>
@ 2014-03-19 22:08 ` Vik Fearing <vik.fearing@dalibo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Vik Fearing @ 2014-03-19 22:08 UTC (permalink / raw)
To: AlexK <alkuzo@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can I use a constraint to make sure all array elements are positive?
2014-03-19 21:59 Can I use a constraint to make sure all array elements are positive? AlexK <alkuzo@gmail.com>
@ 2014-03-19 22:20 ` Sergey Konoplev <gray.ru@gmail.com>
2014-03-19 22:35 ` Re: Can I use a constraint to make sure all array elements are positive? Vik Fearing <vik.fearing@dalibo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Sergey Konoplev @ 2014-03-19 22:20 UTC (permalink / raw)
To: AlexK <alkuzo@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can I use a constraint to make sure all array elements are positive?
2014-03-19 21:59 Can I use a constraint to make sure all array elements are positive? AlexK <alkuzo@gmail.com>
2014-03-19 22:20 ` Re: Can I use a constraint to make sure all array elements are positive? Sergey Konoplev <gray.ru@gmail.com>
@ 2014-03-19 22:35 ` Vik Fearing <vik.fearing@dalibo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Vik Fearing @ 2014-03-19 22:35 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; AlexK <alkuzo@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-03-19 22:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-19 21:59 Can I use a constraint to make sure all array elements are positive? AlexK <alkuzo@gmail.com>
2014-03-19 22:08 ` Vik Fearing <vik.fearing@dalibo.com>
2014-03-19 22:20 ` Sergey Konoplev <gray.ru@gmail.com>
2014-03-19 22:35 ` Vik Fearing <vik.fearing@dalibo.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox