Exclusion constraint with custom operator not working as expected

["Rhys A.D. Stewart" <rhys.stewart@xxxxxxxxx>]

Greetings All,

Firstly, apologies for cross posting. 

I would like to create a table which will contain postGIS geometries, specifically linestrings.  Each line string should be unique, unique in the sense that no linestring should st_equals any other. (see https://postgis.net/docs/manual-3.1/ST_Equals.html)

So, LINESTRING(10 10, 50 50) and LINESTRING(50 50, 10 10) are "st_equal".

 I did the following:

BEGIN;

DROP OPERATOR IF EXISTS |*| (geometry, geometry) CASCADE;

CREATE OPERATOR |*| (
FUNCTION = st_equals,
LEFTARG = geometry,
RIGHTARG = geometry,
COMMUTATOR = |*|
);

CREATE OPERATOR CLASS my_ops FOR TYPE geometry
USING gist FAMILY gist_geometry_ops_2d AS
OPERATOR 99 |*| (geometry, geometry);

-- This returns True
SELECT  'LINESTRING(10 10, 50 50)'::geometry |*| 'LINESTRING(50 50, 10 10)'::geometry;

DROP TABLE IF EXISTS test_1 ;
CREATE TABLE test_1 (
fid integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
g geometry,
EXCLUDE USING GIST (g WITH |*|)
);

INSERT INTO test_1 (g) VALUES ('LINESTRING(10 10, 50 50)') ON CONFLICT DO NOTHING;
INSERT INTO test_1 (g) VALUES ('LINESTRING(50 50, 10 10)') ON CONFLICT DO NOTHING; -- This should do nothing;

SELECT fid, st_astext(g) FROM test_1; -- both rows returned, exclusion doesn't work as I think it should.

ROLLBACK;

But where I expected the second insert to 'DO NOTHING', it very much did something. So clearly I am missing something somewhere or my understanding of exclusion constraints is lacking...or both.  Any suggestions to get the desired outcome? (Using a trigger doesn't count :-D )

But

Rhys

Peace & Love | Live Long & Prosper