From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andy Shellam <andy-lists@networkmail.eu>
Cc: pgsql-sql@postgresql.org
Subject: Re: CHECK constraint removing brackets
Date: Mon, 11 Jan 2010 15:15:18 -0500
Message-ID: <18738.1263240918@sss.pgh.pa.us> (raw)
In-Reply-To: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu>
References: <D884BF27-7555-40BA-8A5E-CAEE1272CE26@networkmail.eu>
Andy Shellam <andy-lists@networkmail.eu> writes:
> 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)
> 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
There is no "conversion" there, that means exactly the same thing.
(AND binds tighter than OR.)
I think your real problem is that you're trying to use "= NULL" and
"!= NULL" where you should say IS NULL or IS NOT NULL.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, andy-lists@networkmail.eu
Subject: Re: CHECK constraint removing brackets
In-Reply-To: <18738.1263240918@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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