pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCHECK constraint removing brackets
5+ messages / 3 participants
[nested] [flat]
* CHECK constraint removing brackets
@ 2010-01-11 19:49 Andy Shellam <andy-lists@networkmail.eu>
0 siblings, 2 replies; 5+ messages in thread
From: Andy Shellam @ 2010-01-11 19:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK constraint removing brackets
@ 2010-01-11 20:14 Scott Marlowe <scott.marlowe@gmail.com>
parent: Andy Shellam <andy-lists@networkmail.eu>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2010-01-11 20:14 UTC (permalink / raw)
To: Andy Shellam <andy-lists@networkmail.eu>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK constraint removing brackets
@ 2010-01-11 20:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andy Shellam <andy-lists@networkmail.eu>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2010-01-11 20:15 UTC (permalink / raw)
To: Andy Shellam <andy-lists@networkmail.eu>; +Cc: 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_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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK constraint removing brackets
@ 2010-01-11 23:27 Andy Shellam <andy-lists@networkmail.eu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Andy Shellam @ 2010-01-11 23:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi Tom and Scott,
>
> 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.
Argh such a school-boy error! This is the first bit of database programming I've done for about 2 months, and I hadn't switched my C++ brain off.
I know about the <> and !=, for some reason != has always made better sense to me to read, so I tend to write it that way.
Cheers,
Andy
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CHECK constraint removing brackets
@ 2010-01-12 00:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andy Shellam <andy-lists@networkmail.eu>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-01-12 00:10 UTC (permalink / raw)
To: Andy Shellam <andy-lists@networkmail.eu>; +Cc: pgsql-sql
Andy Shellam <andy-lists@networkmail.eu> writes:
> I know about the <> and !=, for some reason != has always made better sense to me to read, so I tend to write it that way.
Yeah, a lot of people prefer != ... that's why we provide it as an
alias for <>. There's no functional difference.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-01-12 00:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-11 19:49 CHECK constraint removing brackets Andy Shellam <andy-lists@networkmail.eu>
2010-01-11 20:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-01-11 20:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-01-11 23:27 ` Andy Shellam <andy-lists@networkmail.eu>
2010-01-12 00:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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