From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Josh Berkus" <josh(at)agliodbs(dot)com> |
Cc: | "guard" <guard(at)ficnet(dot)net>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: nullif BUG??? |
Date: | 2001-07-29 03:15:23 |
Message-ID: | 197.996376523@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> Er... what were you expecting, exactly?
AFAICT, the quoted behavior is correct per the defined behavior of
nullif(), cf
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-conditional.html
NULLIF(value1, value2)
The NULLIF function returns NULL if and only if value1 and
value2 are equal. Otherwise it returns value1.
> Except for IS NULL (and COALESCE, which uses IS NULL) any operation
> involving a NULL is also NULL.
Well, that's not quite the correct reasoning.
NULLIF and COALESCE are both shorthands for CASE expressions, and hence
are capable of returning non-NULL for a NULL input. It all depends on
how the CASE tests are phrased. NULLIF is essentially
CASE WHEN value1 = value2 THEN NULL ELSE value1 END
In the quoted example, "NULL = 5" will yield NULL, which is interpreted
as a FALSE case test, so you get the ELSE case, ie value1, ie NULL.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Mark kirkwood | 2001-07-29 04:30:13 | Re: performance issue with distance function |
Previous Message | Stephan Szabo | 2001-07-28 19:18:40 | Re: Who do I make _ not a wildcard? |