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. Is unique constraint using the function the way to go here, or do I need to consider some other approach like a trigger, or exclusion constraint?

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)"
  • Exclusion Constraints are index operator based, and = (binary equal) is all you can hope for. Triggers, Rules or text based geometry representation (MD5(ST_AsBinary()), ST_GeoHash()) are your options. I'd go with a Trigger.
    – geozelot
    Aug 20, 2020 at 20:22
  • @geozelot: can you give me an example of how this would look like or a some psuedo code? I am just wondering whether, I would be inserting the row, then check if any exists using ST_EQUALS, and then delete the row if duplicate, and perform addition. Aug 20, 2020 at 21:53
  • Wouldn't && work for Exclusion Constraint? See list of PostGIS operators. > && — Returns TRUE if A's 2D bounding box intersects B's 2D bounding box. But it seems like it will not check for exact equality, only intersection. Dec 1, 2021 at 15:01


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.