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,
RETURNS boolean AS
      SELECT 1
      FROM   geomtable AS a
      WHERE  a.gid <> id
        AND  a.geom && gm
        AND  ST_Equals(a.geom, gm)

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)"
  • I assume that the index entry size limit keeps you from using a plain unique constraint. An idea (with potential false positive errors) would be a unique index on md5(geom). Aug 24 '20 at 6:35
  • @LaurenzAlbe: Thanks, I was trying this from another answer, could you expand on the possibility of 'false positives'? Where could I find out more about this? Aug 24 '20 at 22:54
  • Two different geometries could have the same MD5 hash. Aug 25 '20 at 1:59
  • @LaurenzAlbe: Isn't it highly unlikely: stackoverflow.com/questions/1756004/… Aug 25 '20 at 2:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.