Kevin Grittner <kgrittn@ymail.com> writes:
> I recall two constructs that we had in production that caused some
> pain moving to PostgreSQL.
> Here's one:
> test=# insert into x values (coalesce(null, null));
> ERROR: column "d" is of type date but expression is of type text
I don't have a lot of sympathy for that one. coalesce(null, null)
isn't legal at all per SQL spec, for essentially the reason SQL Server
gives:
> At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
Otherwise the result type of coalesce() isn't well-defined, and there is
nothing at all in the spec that would suggest looking to surrounding
context to decide that. Our choice to resolve it as text rather than
failing is admittedly a bit arbitrary, but I don't find it unreasonable.
> 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
Yeah, this one is a bit annoying, especially considering we do get it
right in related cases:
regression=# select null as ts union all (select null union all select now());
ts
-------------------------------
2015-05-03 13:05:30.639594-04
(3 rows)
It's possible this could be fixed with some rejiggering of parse analysis
so that matching of output-column types is performed across a whole
set-operation tree at once rather than on binary pairs of leaf queries.
On the other hand, a case could be made that such behavior would also be
in violation of the standard, which is perfectly clear that you process
set operations as binary pairs not holistically. There would certainly
be some compatibility risk involved in changing the resolution behavior
like that, especially for cases where the type choice affects the set
operation's behavior significantly.
regards, tom lane