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
