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 ?
begin; insert into...SELECT...ST_geomFromText(...). Well...new challenge I guess : why this QGIS behavior ? – Leehan Jan 27 '22 at 14:36unobs_rest, the geometrytype ismultisurface. But all the polygons inunobs_restaremultipolygon! So no duplicates for PostGres when I save. I have to find a way to get rid ofmultisurfacein this case. – Leehan Jan 27 '22 at 15:07