Re: Differences in WHERE clause of SELECT

Robert Haas <robertmhaas@gmail.com> wrote:

> We can certainly continue to play whack-a-mole and dream up a new
> solution every time a really intolerable variant of this problem comes
> up.  But that doesn't seem good to me.  It means that every case
> behaves a little different from every other case, and the whole thing
> is kinda arcane and hard to understand, even for hackers.

If you're building up a list of things that generate errors in
PostgreSQL but not other DBMS products, make sure you have this:

test=# create table t(d date);
CREATE TABLE
test=# insert into t values (NULL);
INSERT 0 1
test=# insert into t values (COALESCE(NULL, NULL));
ERROR:  column "d" is of type date but expression is of type text
LINE 1: insert into t values (COALESCE(NULL, NULL));
                              ^
HINT:  You will need to rewrite or cast the expression.

From a user perspective, it's hard to explain why COALESCE(NULL,
NULL) fails in a location that a bare NULL works.  From the
perspective of those working on the code, and looking at the
problem from the inside out, it seems sane; but that's the only
perspective from which it does.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company