From: Lew <noone@lewscanon.com>
Subject: Re: check constraint bug?
Date: Sat, 07 May 2011 08:52:21 -0400
Organization: albasani.net
Lines: 34
Message-ID: <iq3fa3$b2o$1@news.albasani.net>
References: <20110506060608.12bd24b0@dick.coachhouse>
	<BANLkTimt7GMbEgkRtLvKFy+kK8KDOnn2Og@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Info: news.albasani.net;
	logging-data="7IrIRuGpzVBvkey3AcnNSMHNIyJ/GWFVF3WfHqFxcGwmMOA5L9Lo7WyjU2Qxb7fsM+2ZeHKZ+rkVHQyoGQ+15q/o2/GaqzJlvSQ/oDnPvrdaPiEONJw3wgJbziTpUpeR";
	mail-complaints-to="abuse@albasani.net"
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10
In-Reply-To: <BANLkTimt7GMbEgkRtLvKFy+kK8KDOnn2Og@mail.gmail.com>
Cancel-Lock: sha1:4nlMSULcxVGh+S6ngAzLsv7iARA=
To: pgsql-sql@postgresql.org

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