3

I have a table of points, and I set some time ago a simple unique constraint as such : 

ALTER TABLE points ADD CONSTRAINT points_geom_key UNIQUE (geom);

It generally worked fine, until now that I face a weird behaviour. I tried to directly copy/paste some points through QGIS with the copy/paste of entities, as I am generally doing, but got an error saying the constraint is not being respected. Long story short : I have two points that are nowher near each other, for which the geom field looks totally different.

I guess this is not the way to check for the unicity of the geom, so what would be the proper way?

I run PostgreSQL 9.4 and PostGIS 2.2

GuiOm Clair
  • 1,191
  • 9
  • 25
  • Doing copy/paste puts the new point at the same location as the source – JGH Jun 18 '18 at 11:33
  • I´d recommend to update your DB. while a UNIQUE CONSTAINT is arguably still not the way to check for geometry uniqueness, and I might be wrong here, I´d say the culprit is how, in PostgreSQL < 9.5, the = operator works on geometries (i.a. here), since the uniqueness of that constraint is enforced via (unique) btree index, and thus the = will be used on the geometry type. you could try to use that constraint on a new bytea column with the value of ST_AsBinary(geom) or use a trigger to compare with ST_Equals. – geozelot Jun 18 '18 at 11:44
  • @JGH Hum, I must say that I guess I do not understand what you mean. I actually dropped the constraint and added the points, and I clearly see that they are not at the same place. Furthermore, I did the copy/paste operation many times and it worked just fine, with the locations going just right, but with the "Copy feature" button. – GuiOm Clair Jun 18 '18 at 11:46
  • @ThingumaBob Thank you for your comment. I cannot upgrade my DB (I actually had to downgrade it from 9.5 in order to include it on the company GIS server, which runs on 9.4...). I'll look into your suggestions. – GuiOm Clair Jun 18 '18 at 11:51

0 Answers0