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