0

PostgreSQL 13.5, PostGIS 3.2, Windows server 2012

Here is the table I have created

CREATE TABLE IF NOT EXISTS activite.unobs_rest
(
gid integer NOT NULL,
numunobs character varying(20) COLLATE pg_catalog."default",
typunobs character varying(20) COLLATE pg_catalog."default",
interpret character varying(60) COLLATE pg_catalog."default",
datedebut bigint,
datefin integer,
gidoperef integer NOT NULL,
geom geometry(Geometry,2154),
periode character varying(50) COLLATE pg_catalog."default",
CONSTRAINT pk_gid PRIMARY KEY (gid)
)

The table contains 412859 rows.
Here are the partial indexes :

create unique index idx_2col_geom_numunobs on unobs_rest (MD5(st_asbinary(geom)), numunobs) where numunobs is not null;
create unique index idx_1col_geom on unobs_rest (MD5(st_asbinary(geom))) where numunobs is null;    

Indexes are supposed to prevent duplicates when inserting a row with the same geom&numunobs or the same geom&numunobs IS NULL of an existing row.

But this doesn't work.

After reading Postgres partial index on IS NULL not working, I have updated the statistics, even if I don't run select query :

analyze activite.unobs_rest (geom)
analyze activite.unobs_rest (numunobs)

But again, I can copy and paste an existing row without any problem, which I don't want.

question Is there a way to know if indexes are used when I try to insert a row in unobs_rest ?

Leehan
  • 207
  • 2
  • 15
  • Please show us how you "copy and paste" existing rows. –  Jan 27 '22 at 14:09
  • I am using QGis 3.16 in edition session : select a polygon, copy and paste. But I just realized that I have the expected error when I try to insert two times the same polygon with a transaction in pgAdmin4 : begin; insert into...SELECT...ST_geomFromText(...). Well...new challenge I guess : why this QGIS behavior ? – Leehan Jan 27 '22 at 14:36
  • QGIS cannot insert anything in the database that the database doesn't allow. So either the row is not inserted, or the values differ (however slightly). – Laurenz Albe Jan 27 '22 at 14:54
  • Exactly, the point is when using QGIS on unobs_rest, the geometrytype is multisurface. But all the polygons in unobs_rest are multipolygon ! So no duplicates for PostGres when I save. I have to find a way to get rid of multisurface in this case. – Leehan Jan 27 '22 at 15:07
  • @Leehan If you are inserting multisurfaces, how can the table not be having multisurfaces in it? If you have a before trigger that converts multisurfaces to multipolygons, then the constraint will be applied after the conversion. – jjanes Jan 27 '22 at 19:04
  • @jjanes of course. But I discovered that QGIS was inserting multisurfaces - instead of mulitpolygons like I was thinking - today ! And yes I will create a trigger to convert multisurfaces. – Leehan Jan 27 '22 at 19:38

0 Answers0