Polygons with same geometry and bbox but can't be aggregated with GROUP BY

Question

I'm trying to create a constraint unique on a geometry column to avoid duplicate ones, following https://dba.stackexchange.com/questions/299098/why-doesnt-my-unique-constraint-trigger/299107#299107

The function to populate the new column geom_text (since constraint unique on geometry column is impossible from what I understand)

CREATE OR REPLACE function activite.conversion_geom(geom geometry)
returns varchar
Language SQL immutable as
'select Box2D(geom)';

Alter table activite.ouverture
add column geom_text varchar generated always as (activite.conversion_geom(geom)) stored,
add constraint geom_text unique (geom_text)

I can't create the unique constraint because it says duplicate exists.
But when I execute

select geom from activite.ouverture
group by geom
having count(geom) > 1

No rows is returned.
Fine.

Using the coords in the error message and QGIS

 La clé (geom_text)=(BOX(859646.1872923545 6933903.321689525,859651.5708820212 6933910.993510331)) est dupliquée.

I can see two polygons that seem identical.

So to confirm, using the gid of the polygons

select st_equals(o1.geom,o2.geom)
from activite.ouverture o1, activite.ouverture o2
where o1.gid = 230605 and o2.gid = 230606

returns true ! Why the query with the GROUP BY geom does not find these two polygons ? And I am afraid that there are not alone...

Answer 1

Two completely different geometries can have the same bounding box:

select box2d('polygon((0 0, 1 0, 10 10, 0 0))'::geometry), 
       box2d('polygon((0 10, 1 10, 10 0, 0 10))'::geometry);
     box2d      |     box2d
----------------+----------------
 BOX(0 0,10 10) | BOX(0 0,10 10)
(1 row)

Also, st_equals considers spatial equality while = (used in group by considers plain equality, so if the vertices are not the same, such as changing the point order, = will be false while st_equals will be true

with src(geom1,geom2) as (values (
   'polygon((0 0, 1 0, 10 10, 0 0))'::geometry,
   'polygon((0 0, 10 10, 1 0, 0 0))'::geometry))
select st_equals(geom1,geom2), 
       geom1 = geom2
from src;

 st_equals | ?column?
-----------+----------
 t         | f
(1 row)