DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=glzew8A4sD9dYNmuGCRo3cR7W2LCvHzFWnpRkHxS2A72RFKi+7ifkPRJpvdPCI1xEC
	yznzH7mza314nlcdgGbKwPfyb4uFPuZbV9ETa3lu0LsiTAH7s0FDV8SASGjBqpAJu2//
	BJ6eGBi01lvXM8xJHjj6jJ/aoRmF/fuwk7wK4=
MIME-Version: 1.0
In-Reply-To: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu>
References: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu>
Date: Mon, 11 Jan 2010 13:14:12 -0700
Message-ID: <dcc563d11001111214p3cd20de4rf65d8d591b3e4cca@mail.gmail.com>
Subject: Re: CHECK constraint removing brackets
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Andy Shellam <andy-lists@networkmail.eu>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Jan 11, 2010 at 12:49 PM, Andy Shellam
<andy-lists@networkmail.eu> wrote:
> With the above in mind, I decided on the following check to enforce this:
>
> (state = 'Unconfirmed'::client.order_state AND invoice_id = NULL) OR (state != 'Unconfirmed'::client.order_state AND invoice_id != NULL)

Nothing can = null.  and invoice_id IS NULL is the proper
nomenclature.  Also, something <> NULL makes no sense, because we
don't know what NULL is, so that becomes something IS NOT NULL

Also != is not proper SQL, although many dbs understand it, <> is the
proper way to write NOT EQUAL TO.

> However PostgreSQL (8.4.2) converts this to the following:
>
> state = 'Unconfirmed'::client.order_state AND invoice_id = NULL::integer OR state <> 'Unconfirmed'::client.order_state AND invoice_id <> NULL::integer

ANDs have priority of ORs so the removal of the parenthesis makes no
great change here.  also, SQL standard is <> not !=.

I'm guessing the real problems here are your NULL handling.  See if
changing it to IS NULL / IS NOT NULL gets you what you want.