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

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!

• Please add an example of your SQL query. Is your question actually something about "How does UNION in PostgreSQL deal with BLOB fields?" Jun 1 at 9:17

`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).

• Note that you also may find floating point discrepancies for the same computed geometry between different systems/OS. Jun 1 at 9:46
• Thank you very much @geozelot. If I could give this 10 votes, I would!
– MDB
Jun 1 at 11:26