Sample Header Ad - 728x90

Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts

0 votes
2 answers
429 views
I have a simple table with a geometry column like so:
CREATE TABLE geomtable 
(
  gid serial, 
  geom geometry, 
  trip_count integer
);
I want a unique constraint on the geometry column, such that if the same geometry is being inserted again then, the ON CONFLICT clause should add the trip_count to the existing row. So my insert query looks something like this:
INSERT INTO geomtable (geom, trip_count) values (, 123) 
ON CONFLICT ON CONSTRAINT 
DO UPDATE SET trip_count = geomtable.trip_count + EXCLUDED.trip_count;
I tried the approach to enforcing equality constraint as described here like so:
CREATE FUNCTION equality_constraint_func(
  id INT,
  gm GEOMETRY
)
RETURNS boolean AS
  $$
    SELECT NOT EXISTS (
      SELECT 1
      FROM   geomtable AS a
      WHERE  a.gid  id
        AND  a.geom && gm
        AND  ST_Equals(a.geom, gm)
    );
  $$
  LANGUAGE sql
;
And adding a CHECK constraint like so :
ALTER TABLE geomtable
  ADD CONSTRAINT equality_constraint
  CHECK (equality_constraint_func(gid, geom))
;
**However, this does not help me perform the DO UPDATE part of conflict resolution.** It just raises an error on duplicate geometry. I of course can't create a primary key out of my geom column as I get the index row requires 42632 bytes, maximum size is 8191 error. I get a similar error on creating a unique index on the geom column. I tried creating a unique constraint using the above syntax by replacing CHECK with UNIQUE, but I get a syntax error. How would I go writing a trigger to enforce the unique constraint? Specifically, how do I handle the conflict case? Let the insert happen as usual and then perform a delete etc. My version information is as under:
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"
Asked by Chintan Pathak (143 rep)
Aug 23, 2020, 08:11 PM
Last activity: Jul 23, 2025, 02:56 PM