Here is a minimal reproduction:
SELECT 1
UNION ALL
SELECT DISTINCT NULL
Fails with:
UNION types numeric and text cannot be matched
According to the PostgreSQL documentation on Type Resolution for UNION, CASE, and Related Constructs and the following chapter on SELECT Output Columns, the type of null
should be derived from the preceding select.
In fact it works, if I remove the DISTINCT
:
SELECT 1
UNION ALL
SELECT NULL
Why does the DISTINCT
modifier interfere with type resolution?
Best Answer
Without the
DISTINCT
, the type of the second part of theUNION ALL
isunknown
:This gets resolved to
integer
.If there is a
DISTINCT
, PostgreSQL has to resolve the type ofNULL
earlier on: to eliminate duplicates, it has to know which equality operator to use. Lacking other information, it choosestext
as the preferred type of the string category.Then you get the error because
integer
andtext
cannot be reconciled.The documentation describes the type choice for the equality operator as follows: