When creating a PostGIS table (e.g. 'lakes') in the DB Manger a primary key ('lakes_pkey'), a index ('sidx_lakes_geom) and a sequence ('lakes_gid_seq') are also created.

enter image description here

But when renaming the table (e.g. 'lakes_2015'), the constraint, the index and the sequence don't get new names.

enter image description here

So it's not possible to create a new table named 'lakes' after renaming the original table 'lakes'.

Error: Relation „sidx_lakes_geom“ already exists

Is this a bug? (https://hub.qgis.org/issues/13823)

  • What is your question? – raphael Feb 20 '16 at 3:15
  • Is it a bug or a missing feature that the constraint, the index and the sequence don't get new names when renaming the table in the DB Manager? – Lunar Sea Feb 20 '16 at 7:32
  • There is no strict rules to name indexes and sequence. DB Manager follows this naming schema. You may face to similar problem if DB Manager would rename indexes and sequences and those names are already used in the database. Rename them manually using psql or pgadmin. – Zoltan Feb 20 '16 at 7:46

It's a bug, but only in relation to the index. The way PostgreSQL automatically creates primary keys and sequences should normally take care of this kind of name collision. The problem is that QGIS is creating the spatial index and whoever programmed that feature didn't think of your use-case.

To avoid this, instead of renaming your table. Create a new target table and INSERT your data from your source table to the target table before then truncating your source table.

  • Thanks for your answer, but I suppose this will cause new issues when a view depends on the source table. – Lunar Sea Feb 20 '16 at 11:38
  • Similar issue: gis.stackexchange.com/questions/164674/… – Lunar Sea Feb 20 '16 at 12:11
  • Care to elaborate on your concern regarding views? And can you explain what you're trying to accomplish with renaming? – raphael Feb 20 '16 at 23:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.