Hi, I notice this had been raised as a bug (and subsequently over-ruled) so I'm asking how I can achieve the following business rule.
I have an order table which has an invoice_id column that links to an invoice table (an order can only have 1 invoice, but one invoice can have multiple orders.) An order can have either an unconfirmed state, or any other state after it's been confirmed. If an order has the state unconfirmed, the invoice_id column must be null, as an invoice won't have been created yet. If an order has any other state except unconfirmed, the invoice_id must not be null. 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 This allows both an order state of "unconfirmed" and a non-null invoice_id, and an order state of "confirmed" and a NULL invoice_id. How can I achieve the above? Thanks, Andy -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
