Re: CHECK constraint removing brackets - Mailing list pgsql-sql

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_stateAND 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