Repository navigation
can't init dbsync when table has an index on id column? #148
Description
I can't seem to start dbsync , initialised from the database when my table has a btree index on the id column.
CREATE TABLE my_polygon_table (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
comment text,
date date,
recorded_by text,
validated boolean,
validated_by text,
geom geometry(Polygon,4326)
);
CREATE INDEX IF NOT EXISTS my_polygon_table_geom_gist
ON my_polygon_table USING gist (geom);
CREATE INDEX IF NOT EXISTS my_polygon_table_id_btree
ON my_polygon_table USING btree (id);I get this error in dbsync :
Error: postgres cmd error: ERROR: column "id" specified more than once
|
|
| SQL:
| CREATE TABLE "z_for_mergin_use_only_db_sync"."my_polygon_table" ("id" integer NOT NULL, "id" integer NOT NULL, "comment" text, "date" date, "recorded_by" text, "validated" boolean, "validated_by" text, "geom" geometry(Polygon,4326), PRIMARY KEY ("id"));
However if I do the same without creating an index it works fine.
Activity
CeliaBuira commentedon Feb 6, 2025
Hi @geographit2 thanks for the bug report that's indeed looks bad to not be able to use btree, I'll give it a try on my end with the SQL you provided, thanks you
wojtek19022 commentedon Jun 21, 2025
Hi @ValentinBuira & @geographit2 I actually encountered the same problem but using only GIST and regular indexes. This problem didn't appear due to a use of different indexing methods. It appeared due to a use of 2 indexes. In a latest db-sync version only one index is supported on one column.
Wisarb commentedon Jun 11, 2026
I've been struggling with this today. Behaviour is tricky to replicate, had to strip out all my indexes and put them back in one by one.
Tables can have multiple indexes.
Indexes can refer to same name columns in different tables. so...
CREATE INDEX ... ON Inspections (date);
CREATE INDEX ... ON works (date);
CREATE INDEX ... ON Inspections (tree_id);
CREATE INDEX ... ON Inspections (date);
work. but...
CREATE INDEX ... ON Inspections (date);
CREATE INDEX ... ON Inspections (tree_id, date);
does not, two indexes target the same column. DB_Sync for some reason sees the date column twice and you get the error shared by @geographit2
As Postgres automatically indexes the PK column, I suspect that when @geographit2 made a index for his id column, he was creating a duplicate index alongside the automatic postgres one.
I replicated the suspicion and with no other indexes on a table, added an index for the PK column and got the same error.
hope this helps.