pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcheck constraint bug?
3+ messages / 3 participants
[nested] [flat]
* check constraint bug?
@ 2011-05-06 05:06 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: Tarlika Elisabeth Schmitz @ 2011-05-06 05:06 UTC (permalink / raw)
To: pgsql-sql
I specified:
ALTER TABLE h ADD CONSTRAINT val_h_stats
CHECK (NOT (sex = 'f') AND (stats IS NOT NULL));
which was translated to:
ALTER TABLE h ADD CONSTRAINT val_h_stats
CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL);
Can the expression not be bracketed?
I changed this to:
ALTER TABLE horse ADD CONSTRAINT val_horse_stats
CHECK (sex != 'f') OR (stats IS NULL));
--
Best Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: check constraint bug?
@ 2011-05-06 05:10 Scott Marlowe <scott.marlowe@gmail.com>
parent: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Marlowe @ 2011-05-06 05:10 UTC (permalink / raw)
To: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>; +Cc: pgsql-sql
On Thu, May 5, 2011 at 11:06 PM, Tarlika Elisabeth Schmitz
<postgresql3@numerixtechnology.de> wrote:
> I specified:
>
> ALTER TABLE h ADD CONSTRAINT val_h_stats
> CHECK (NOT (sex = 'f') AND (stats IS NOT NULL));
>
> which was translated to:
>
> ALTER TABLE h ADD CONSTRAINT val_h_stats
> CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL);
You need another level of parens:
CHECK (NOT ((sex = 'f') AND (stats IS NOT NULL)));
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: check constraint bug?
@ 2011-05-07 12:52 Lew <noone@lewscanon.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Lew @ 2011-05-07 12:52 UTC (permalink / raw)
To: pgsql-sql
Scott Marlowe wrote:
> Tarlika Elisabeth Schmitz wrote:
>> I specified:
>>
>> ALTER TABLE h ADD CONSTRAINT val_h_stats
>> CHECK (NOT (sex = 'f') AND (stats IS NOT NULL));
>>
>> which was translated to:
>>
>> ALTER TABLE h ADD CONSTRAINT val_h_stats
>> CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL);
> You need another level of parens:
>
> CHECK (NOT ((sex = 'f') AND (stats IS NOT NULL)));
Because NOT has higher precedence than AND.
<http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-PRECEDENCE;
Note that equals (=), IS and NOTNULL have higher precedence than NOT.
So the CHECK expression Scott indicated is equivalent to the parenthesis-minimal
CHECK ( NOT ( sex = 'f' AND stats IS NOT NULL ) )
or
CHECK ( sex != 'f' OR stats IS NULL )
--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-07 12:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-06 05:06 check constraint bug? Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
2011-05-06 05:10 ` Re: check constraint bug? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-07 12:52 ` Re: check constraint bug? Lew <noone@lewscanon.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox