From: | "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com> |
---|---|
To: | PostGIS Users Discussion <postgis-users(at)lists(dot)osgeo(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Exclusion constraint with custom operator not working as expected |
Date: | 2021-06-22 20:29:34 |
Message-ID: | CACg0vTmVJD64Tm1CPeJduAQfPzuBoZFS2zFXcv0YUxLkXfMYPg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Niels Jespersen | 2021-06-23 04:57:09 | SV: cpu-intensive immutable function and parallel scan |
Previous Message | Vijaykumar Jain | 2021-06-22 18:06:40 | Re: www.postgresql-archive.org content deleted |