Search code examples
sqldatabasepostgresql

What criteria is used by UNION to make result rows "DISTINCT"?

The manual says:

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

But it doesn't specify what criteria is used under the hood. Is it DISTINCT based on the PKs? Based on all columns from the query?

What are the criteria used to eliminate repeated rows?

Solution

  • It's based on equality of the whole row.

    The manual references the criteria of DISTINCT, which are spelled out in the manual as:

    Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison.

    Compatible column data types are coerced to a common type as long as type resolution arrives at a common denominator based on the given rules.