Gorm AutoMigrate fails if custom index is already added to the table (Duplicate column error)

GORM Playground Link

https://github.com/go-gorm/playground/pull/469

Description

AutoMigrate fails if custom index is already added to the table (Duplicate column error)

Comment From: glebarez

This is due to SQLite driver does not expect index name in CREATE INDEX to be written without any quotes. Fix PR opened: https://github.com/go-gorm/sqlite/pull/90

The misleading error duplicate column name: id was due to bug in GORM migrator (Fix filed in https://github.com/go-gorm/gorm/pull/5283)

Comment From: LouisSayers

Not just a Sqlite issue, I just got this in postgres as well with the latest gorm and postgres driver versions

I'm getting errors that look like: relation "idx_signup_requests_hash_code" already exists (SQLSTATE 42P07)

With Versions: gorm.io/gorm v1.23.4 gorm.io/driver/postgres v1.3.4

Have had issues with this for a number of weeks now and having to do some hacky stuff to get migrations working...

Comment From: khalilsarwari

I'm using the latest versions:

gorm.io/datatypes v1.0.7 gorm.io/driver/postgres v1.3.8 gorm.io/gorm v1.23.7

And am still running into the same issue:

relation "idx_users_user_name" already exists (SQLSTATE 42P07)

Comment From: khalilsarwari

I switched back to the following versions and the issue went away:

gorm.io/datatypes v1.0.2 gorm.io/driver/postgres v1.1.1 gorm.io/gorm v1.21.15

Comment From: a631807682

@khalilsarwari It is work for me in lastest version, please provide it in https://github.com/go-gorm/playground

Comment From: khalilsarwari

@a631807682 You are right, it is also working for me even when I try

gorm.io/datatypes v1.0.7 gorm.io/driver/postgres v1.3.8 gorm.io/gorm v1.23.7

I'm not too sure why it was giving me the same error, perhaps I was not updating the versions correctly.

Comment From: PaulSonOfLars

I can reproduce this on postgres, using versions:

gorm.io/driver/postgres v1.3.8 (previously 1.1.0)
gorm.io/gorm v1.23.8 (previously 1.21.13)

TL;DR:

The trick is to create a table with both an index AND a constraint on the same column. This causes the automigration to fail.

Steps:

My production database originally didn't use gorm, so has some manually created constraints (backed by indexes, because postgres) Namely, the output of \d+ <tablename> shows the following indexes:

Indexes:
...
    "idx_users_user_id" UNIQUE, btree (user_id)
    "users_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
...

In this environment, when I start up my service, automigration fails with am error: ERROR: relation \"idx_users_user_id\" already exists (SQLSTATE 42P07).

However, if I create a new database purely through gorm (in a dev environment), the result is missing the CONSTRAINT, and that starts up fine:

Indexes:
...
    "idx_users_user_id" UNIQUE, btree (user_id)
...

I can then recreate the error by adding the following constraint in my dev environment (alter table users add constraint "users_user_id_key" unique (user_id)). Upon restarting the service, automigration then fails with the error mentioned above.

Comment From: HeCorr

I'm facing a similar issue today with GORM v1.23.8 and CockroachDB.

Interestingly enough, if you don't set an index name (gorm:"uniqueIndex"), automigration fails on the second run as expected but if you do (gorm:"uniqueIndex:auth_token") it only ever fails on the third run.

For now the simplest workaround is to not make the index unique.

Comment From: HeCorr

Actually, I think my issue is unrelated and I managed to fix it by defining the unique tag separately:

Comment From: PaulSonOfLars

Are there any updates on this? Would be lovely get this fixed.

I'd be happy to give it a go, but I have no familiarity with the codebase and wouldn't know where to start looking. If others don't have time to debug, could they maybe point me in the right direction?

Comment From: glebarez

Are there any updates on this? Would be lovely get this fixed.

I'd be happy to give it a go, but I have no familiarity with the codebase and wouldn't know where to start looking. If others don't have time to debug, could they maybe point me in the right direction?

This issue was originaly filed for Sqlite driver, and fixed afterwards. I suggest you file separate issue for whatever driver you have this failure with.

Comment From: jinzhu

Tested with latest version, should works with sqlite, mysql and postgres.

Comment From: MMN3003

This issue fixed for me when I changed unique index name like:

type User struct {
    Username     string `gorm:"uniqueIndex:idx_username"` // set index name without table name in the middle
}

make sure to remove table name from the index name auto name is like idx_users_username after remove is like idx_username

Comment From: shufps

I didn't give my index any name.

Hash []byte `gorm:"size:49;uniqueIndex"`

it fails with: ERROR: relation "idx_transactions_hash" already exists (SQLSTATE 42P07)

on a Postgres 14 database.

Worked for me with v1.24.2 Didn't work with v1.24.5