Re: DISTINCT ON not working...?

"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