Here is a minimal reproduction:


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:


Why does the DISTINCT modifier interfere with type resolution?

1 Answer 1


Without the DISTINCT, the type of the second part of the UNION ALL is unknown:

SELECT pg_typeof(NULL);

(1 row)

This gets resolved to integer.

If there is a DISTINCT, PostgreSQL has to resolve the type of NULL earlier on: to eliminate duplicates, it has to know which equality operator to use. Lacking other information, it chooses text as the preferred type of the string category.

Then you get the error because integer and text cannot be reconciled.

The documentation describes the type choice for the equality operator as follows:

If any input arguments are unknown, check the type categories accepted at those argument positions by the remaining candidates. At each position, select the string category if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal looks like a string.)

  • Do you know whether this is an intentional design decision, or a technical limitation of the type resolution engine that could be improved in future releases?
    – soc
    Aug 28, 2020 at 15:51
  • I'd say that that is required by the implementation and is not likely to change. You can work around it easily by using SELECT DISTINCT NULL::integer. Aug 28, 2020 at 18:52
  • Thank you for your answer. The use of DISTINCT was dropped instead, which was questionable anyway. (The SQL is not written by hand, but generated; so if it is avoidable to deal with the idiosyncrasies of a specific database, this is preferred.)
    – soc
    Sep 7, 2020 at 13:24
  • Getting rid of DISTINCT is always a good move, particularly for performance. Sep 7, 2020 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.