From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Andy Shellam <andy-lists(at)networkmail(dot)eu> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: CHECK constraint removing brackets |
Date: | 2010-01-11 20:14:12 |
Message-ID: | dcc563d11001111214p3cd20de4rf65d8d591b3e4cca@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Mon, Jan 11, 2010 at 12:49 PM, Andy Shellam
<andy-lists(at)networkmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-01-11 20:15:18 | Re: CHECK constraint removing brackets |
Previous Message | Andy Shellam | 2010-01-11 19:49:50 | CHECK constraint removing brackets |