This has been resolved -- although I still think it may be a bug in
Postgres.

I'm confused as hell, it's Friday, and it's hot though... So I'll have to
think about it over the weekend and let you know if I can make sense of it.

Thanks all for your suggestions.

Cheers,
~p


-----Original Message-----
From: Phillip Smith [mailtohillip.smith@weatherbeeta.com.au]
Sent: Tuesday, 20 February 2007 09:25
To: 'pgsql-sql@postgresql.org'
Subject: DISTINCT ON not working...?

Hi all,

Strange one - I have a nightly export / import routine that exports from one
database and imports to another. Has been working fine for several months,
but last night it died on a unique constraint.

To cut out all the details, the code that is causing the problem:
SELECT DISTINCT ON (ean)
code,
CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
null ELSE ean END AS ean
FROM TMPTABLE
WHERE code NOT IN (SELECT code FROM stock_deleted)
AND ean IS NOT NULL

That is the code that generates the error on the unique constraint against
the ean column.

If I play with that and run this:
SELECT DISTINCT ON (ean)
CASE WHEN ean IS NULL OR valid_barcode(ean) = false THEN
null ELSE ean END AS ean,
count(*)
FROM TMPTABLE
WHERE code NOT IN (SELECT code FROM stock_deleted)
AND ean IS NOT NULL
GROUP BY ean

I get a several thousand rows returned, all with a count(*) of 1, except one
row:
3246576919422 2

DISTINCT ON should eliminate one of those rows that is making that 2 - as I
said, it's been working fine for several months, and it is still doing it
correctly for approximately 100 other rows that have duplicate ean codes.

Can anyone give me a hand to work out why this one is doubling up?!

Cheers,
~p


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly