Thread: check constraint bug?

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

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

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