Does UNION in PostGIS check for distinct geometries, or only other fields?

Question

I've been getting some funny behaviour from UNION vs. UNION ALL when combining two tables in PostGIS, so just wanted to check:

Does UNION include a check for duplicate geometries when determining rows to return, or does it only check other (non-geometric) fields i.e. might UNION (rather than UNION ALL) fail to return a distinct geometry from the second table whose non-geometric fields were identical to a row in the first table?

I checked various bits of PostGIS documentation online but couldn't find the answer. It doesn't help that all searches end up dominated by ST_Union!

Answer 1

UNION does; row equality is checked on all returned columns and their types (using a type resolution sequence).

---

A thing to consider:

The implicit DISTINCT set operator here can be seen as partially enforced using the = operator defined for the given types, and for PostGIS GEOMETRY/GEOGRAPHY types this means bitwise equality - as opposed to spatial equality, i.e. 'LINESTRING(0 0, 1 1)' and 'LINESTRING(1 1, 0 0)' are spatially equal (as per ST_Equals), but not binary equal (as per =):

SELECT ST_Equals('LINESTRING(0 0, 1 1)'::GEOMETRY, 'LINESTRING(1 1, 0 0)'::GEOMETRY) AS "ST_Equals",
       'LINESTRING(0 0, 1 1)'::GEOMETRY = 'LINESTRING(1 1, 0 0)'::GEOMETRY AS "="
;

 ST_Equals | = 
-----------+---
 t         | f
(1 row)

The important part is that no defined SRID is encoded as 0 in the binary representation of a GEOMETRY/GEOGRAPHY type, so

SELECT 'SRID=4326;POINT(0 0)'::GEOMETRY AS geom
UNION
SELECT 'POINT(0 0)'::GEOMETRY AS geom
;

                        geom                        
----------------------------------------------------
 0101000020E610000000000000000000000000000000000000
 010100000000000000000000000000000000000000
(2 rows)

is considered not binary equal (as seen in the returned sequence).

In contrast, the actual geometry type is always encoded as part of the serialization process, so

SELECT 'POINT(0 0)'::GEOMETRY(POINT) AS geom
UNION
SELECT 'POINT(0 0)'::GEOMETRY(GEOMETRY) AS geom
;

                    geom                    
--------------------------------------------
 010100000000000000000000000000000000000000
(1 row)

is considered binary equal (this example is not 100% correct, as the GEOMETRY typemod does actually nothing to the internal byte sequence, but it gets the idea across).