Re: check constraint bug?

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