Exclusion constraint with custom operator not working as expected

--===============3998830929787789230==
Content-Type: multipart/alternative; boundary="0000000000007d662205c560a7f6"

--0000000000007d662205c560a7f6
Content-Type: text/plain; charset="UTF-8"

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

--0000000000007d662205c560a7f6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"auto"><div dir=3D"ltr"><div class=3D"gmail_def=
ault" style=3D"font-family:trebuchet ms,sans-serif">Greetings All,</div><di=
v class=3D"gmail_default" style=3D"font-family:trebuchet ms,sans-serif"><br=
></div><div class=3D"gmail_default" style=3D"font-family:trebuchet ms,sans-=
serif">Firstly, apologies for cross posting.=C2=A0</div><div class=3D"gmail=
_default" style=3D"font-family:trebuchet ms,sans-serif" dir=3D"auto">I woul=
d like to create a table which will contain postGIS geometries, specificall=
y linestrings.=C2=A0 Each line string should be unique, unique in the sense=
 that no linestring should st_equals any other. (see <a href=3D"https://pos=
tgis.net/docs/manual-3.1/ST_Equals.html">https://postgis.net/docs/manual-3.=
1/ST_Equals.html</a>)</div><div class=3D"gmail_default" style=3D"font-famil=
y:trebuchet ms,sans-serif" dir=3D"auto"><br></div><div class=3D"gmail_defau=
lt" style=3D"font-family:trebuchet ms,sans-serif">So, LINESTRING(10 10, 50 =
50) and LINESTRING(50 50, 10 10) are &quot;st_equal&quot;.</div><div class=
=3D"gmail_default" style=3D"font-family:trebuchet ms,sans-serif"><br></div>=
<div class=3D"gmail_default" style=3D"font-family:trebuchet ms,sans-serif">=
=C2=A0I did the following:<br></div><div class=3D"gmail_default" style=3D"f=
ont-family:trebuchet ms,sans-serif"><br></div><div class=3D"gmail_default" =
style=3D"font-family:trebuchet ms,sans-serif"></div><div class=3D"gmail_def=
ault" style=3D"font-family:trebuchet ms,sans-serif">BEGIN; <br><br>DROP OPE=
RATOR IF EXISTS |*| (geometry, geometry) CASCADE;<br><br>CREATE OPERATOR |*=
| (<br>	FUNCTION =3D st_equals,<br>	LEFTARG =3D geometry,<br>	RIGHTARG =3D =
geometry,<br>	COMMUTATOR =3D |*|<br>	);<br><br>CREATE OPERATOR CLASS my_ops=
 FOR TYPE geometry<br>	USING gist FAMILY gist_geometry_ops_2d AS<br>	OPERAT=
OR 99 |*| (geometry, geometry);<br><br>-- This returns True<br>SELECT =C2=
=A0&#39;LINESTRING(10 10, 50 50)&#39;::geometry |*| &#39;LINESTRING(50 50, =
10 10)&#39;::geometry;<br><br>DROP TABLE IF EXISTS test_1 ;<br>CREATE TABLE=
 test_1 (<br>	fid integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,<br>	g g=
eometry,<br>	EXCLUDE USING GIST (g WITH |*|)<br>);<br><br>INSERT INTO test_=
1 (g) VALUES (&#39;LINESTRING(10 10, 50 50)&#39;) ON CONFLICT DO NOTHING;<b=
r>INSERT INTO test_1 (g) VALUES (&#39;LINESTRING(50 50, 10 10)&#39;) ON CON=
FLICT DO NOTHING; -- This should do nothing; <br><br>SELECT fid, st_astext(=
g) FROM test_1; -- both rows returned, exclusion doesn&#39;t work as I thin=
k it should.<br><br>ROLLBACK;</div><div class=3D"gmail_default" style=3D"fo=
nt-family:trebuchet ms,sans-serif"><br></div><div class=3D"gmail_default" s=
tyle=3D"font-family:trebuchet ms,sans-serif">But where I expected the secon=
d insert to &#39;DO NOTHING&#39;, it very much did something. So clearly I =
am missing something somewhere or my understanding of exclusion constraints=
 is lacking...or both.=C2=A0 Any suggestions to get the desired outcome? (U=
sing a trigger doesn&#39;t count :-D )<br></div><div class=3D"gmail_default=
" style=3D"font-family:trebuchet ms,sans-serif"><br></div><div class=3D"gma=
il_default" style=3D"font-family:trebuchet ms,sans-serif">But <br clear=3D"=
all"></div><div><div dir=3D"ltr" data-smartmail=3D"gmail_signature"><div di=
r=3D"ltr"><div><div dir=3D"ltr"><div>Rhys</div>Peace &amp; Love | Live Long=
 &amp; Prosper</div></div></div></div></div></div></div>
</div>

--0000000000007d662205c560a7f6--

--===============3998830929787789230==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users

--===============3998830929787789230==--