<div dir="ltr"><div>Hello,<br><br>The reason why constraint is not working is that GIST scan using your operator does not return what you expect. Deeper debugging needed on your side to fix that select to return the rows you need.<span style="font-family:monospace"><span style="color:rgb(178,104,24)"><br><br><br>12:23:37</span><span style="color:rgb(0,0,0)"> [</span><span style="font-weight:bold;color:rgb(255,84,84)">kom</span><span style="color:rgb(0,0,0)">] > explain select * from test_1 where g |*| 'LINESTRING(10 10,50 50)';
</span><br>┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
<br>│ QUERY PLAN │
<br>├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
<br>│ Index Scan using test_1_g_excl on test_1 (cost=0.14..22.26 rows=635 width=36) │
<br>│ Index Cond: (g |*| '0102000000020000000000000000002440000000000000244000000000000049400000000000004940'::geometry) │
<br>└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
<br>(2 rows)
<br>
<br>Time: 0,916 ms
<br><span style="color:rgb(178,104,24)">12:23:41</span><span style="color:rgb(0,0,0)"> [</span><span style="font-weight:bold;color:rgb(255,84,84)">kom</span><span style="color:rgb(0,0,0)">] > select * from test_1 where g |*| 'LINESTRING(10 10,50 50)'; </span><br>┌─────┬───┐
<br>│ fid │ g │
<br>├─────┼───┤
<br>└─────┴───┘
<br>(0 rows)
<br>
<br>Time: 0,638 ms<br>
<br></span></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Jun 22, 2021 at 11:30 PM Rhys A.D. Stewart <<a href="mailto:rhys.stewart@gmail.com">rhys.stewart@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="auto"><div dir="ltr"><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif">Greetings All,</div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"><br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif">Firstly, apologies for cross posting. </div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif" dir="auto">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 <a href="https://postgis.net/docs/manual-3.1/ST_Equals.html" target="_blank">https://postgis.net/docs/manual-3.1/ST_Equals.html</a>)</div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif" dir="auto"><br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif">So, LINESTRING(10 10, 50 50) and LINESTRING(50 50, 10 10) are "st_equal".</div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"><br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"> I did the following:<br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"><br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif">BEGIN; <br><br>DROP OPERATOR IF EXISTS |*| (geometry, geometry) CASCADE;<br><br>CREATE OPERATOR |*| (<br> FUNCTION = st_equals,<br> LEFTARG = geometry,<br> RIGHTARG = geometry,<br> COMMUTATOR = |*|<br> );<br><br>CREATE OPERATOR CLASS my_ops FOR TYPE geometry<br> USING gist FAMILY gist_geometry_ops_2d AS<br> OPERATOR 99 |*| (geometry, geometry);<br><br>-- This returns True<br>SELECT 'LINESTRING(10 10, 50 50)'::geometry |*| 'LINESTRING(50 50, 10 10)'::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 geometry,<br> EXCLUDE USING GIST (g WITH |*|)<br>);<br><br>INSERT INTO test_1 (g) VALUES ('LINESTRING(10 10, 50 50)') ON CONFLICT DO NOTHING;<br>INSERT INTO test_1 (g) VALUES ('LINESTRING(50 50, 10 10)') ON CONFLICT DO NOTHING; -- This should do nothing; <br><br>SELECT fid, st_astext(g) FROM test_1; -- both rows returned, exclusion doesn't work as I think it should.<br><br>ROLLBACK;</div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"><br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif">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 )<br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif"><br></div><div class="gmail_default" style="font-family:"trebuchet ms",sans-serif">But <br clear="all"></div><div><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div>Rhys</div>Peace & Love | Live Long & Prosper</div></div></div></div></div></div></div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr">Darafei "Komяpa" Praliaskouski<br>OSM BY Team - <a href="http://openstreetmap.by/" target="_blank">http://openstreetmap.by/</a><br></div></div>