Re: Failure to coerce unknown type to specific type

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I have some recollection that we'd also put it off pending
> resolution of debates about how to handle unknown-type literals
> in UNIONs and similar contexts.  But poking at such examples
> right now, the behavior seems generally reasonable: it seems like
> we resolve "unknown" as text when forced to make a decision, but
> otherwise put it off as long as possible. So that consideration
> may be obsolete.

I recall two constructs that we had in production that caused some
pain moving to PostgreSQL.

Here's one:

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

I know these worked in Sybase ASE, SAP DB, MySQL MaxdDB, IBM OS/2
EE's port of DB2, and early versions of MS SQL Server.  I have
confirmed (using SQL Fiddle) that it works in Oracle 11g R2, MySQL
5.5 and 5.6, and SQLite (SQL.js).  Interestingly, MS SQL Server
2014 now throws this error:

At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

Here the other:

test=# select null as ts union all select null union all select now();
ERROR:  UNION types text and timestamp with time zone cannot be matched
LINE 1: ...ect null as ts union all select null union all select now();
^
test=# create table n (id int not null);
CREATE TABLE
test=# insert into n values (1);
INSERT 0 1
test=# select null as ts from n
test-# union all
test-# select null from n
test-# union all
test-# select 1 from n;
ERROR:  UNION types text and integer cannot be matched
LINE 5: select 1 from n;
^

This runs in *all* of the above environments.

I don't know of any other database product which chokes on the above.

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