From: Tom Lane <tgl@sss.pgh.pa.us>
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: DISTINCT ON not working...?
Date: Mon, 19 Feb 2007 23:32:36 -0500
Message-ID: <19666.1171945956@sss.pgh.pa.us> (raw)
In-Reply-To: <003f01c75474$d1ce3480$9b0014ac@wbaus090>
References: <003f01c75474$d1ce3480$9b0014ac@wbaus090>
"Phillip Smith" <phillip.smith@weatherbeeta.com.au> writes:
> 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
Perhaps you've confused yourself by using "ean" as both an input and an
output column name? I think that the "ean" in the DISTINCT ON clause
will effectively refer to that CASE-expression, whereas the one in the
WHERE clause is just referring to the underlying column (and thus making
the IS NULL test in the CASE rather pointless).
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, phillip.smith@weatherbeeta.com.au
Subject: Re: DISTINCT ON not working...?
In-Reply-To: <19666.1171945956@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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