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 (<geom>, 123) ON CONFLICT ON CONSTRAINT <equality_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)"