bigserial type cannot be used in migration (ALTER TABLE)

[atz]

GORM Playground Link

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

Description

We are migrating from gorm v1 (and PG v10). Our model structs that embed gorm.Model have changed their ID from integer to bigserial. This is OK for a fresh database, because PG supports bigserial syntax at CREATE time. It does not support it in ALTER TABLE commands (because strictly speaking, that would involve altering the type AND creating a SEQUENCE).

Example struct:

type GlobalOption struct {
    gorm.Model
    Key   string `gorm:"unique;not null;index"`
    Value string
}

Example SQL for existing table:

CREATE TABLE public.global_options (
    id integer NOT NULL,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    deleted_at timestamp with time zone,
    key text NOT NULL,
    value text
);

During AutoMigrate(), the driver defaults to bigserial here: https://github.com/go-gorm/postgres/blob/5748fd5b2c3921abd487eddab832b4254060b820/postgres.go#L158-L159

Which errors out:

2021/10/11 14:57:23 /Users/atz/go/pkg/mod/gorm.io/driver/postgres@v1.1.2/migrator.go:252 ERROR: type "bigserial" does not exist (SQLSTATE 42704)
[0.876ms] [rows:0] ALTER TABLE "global_options" ALTER COLUMN "id" TYPE bigserial

You can verify that trivially on any version of postgres from v10 to v14:

demo=#CREATE TABLE foobar(id int);
CREATE TABLE
demo=# ALTER TABLE foobar ALTER COLUMN "id" TYPE bigserial;
ERROR:  type "bigserial" does not exist

TL;DR: gorm and driver must distinguish between bigserial for CREATE TABLE and separate logic for ALTER TABLE. It may be that the best approach is not to use bigserial at create time either.

[mdjarv]

I found this issue while searching for a solution to the same problem after upgrading this dependency, and I agree that it's unfortunate (and a bit strange on pg's behalf) that bigserial is not allowed when using ALTER TABLE

For anyone stuck on this, as a temporary workaround I am defining the gorm types manually for the ID property, however this will not work for creating new tables as the AUTO_INCREMENT statement does not seem to apply for type bigint:

type Demo struct {
  ID int `gorm:"type:bigint;primary_key,AUTO_INCREMENT"`
}

[atz]

For migration, my workaround has been to pre-migrate the affected fields:

db.Exec(`ALTER TABLE IF EXISTS mytable ALTER COLUMN "id" TYPE bigint`)

Then AutoMigrate will not attempt to migrate the field.

Obviously, if I am having to manage migrations manually, that diminishes the value of the ORM, but it at least allowed us to get to a contemporary version of gorm.

[jinzhu]

The latest version of postgres driver fixed the AutoMigrate issue.

[roboslone]

It's broken again: https://github.com/go-gorm/gorm/issues/5047 db.Exec hack didn't work in my case :(

[ridwankustanto]

I was okay, but now I face this problem as well, can't auto migrate with bigserial

[hantmac]

I face this problem as well, can't auto migrate with bigserial.

[lichuan0620]

@roboslone @hantmac @ridwankustanto @dhirajsb and anyone who comes across this in the future:

On top of upgrading to the latest version of Postgres driver, you also need to explicitly set the serial field to auto increment like this:

    SomeField int `gorm:"serial;autoIncrement"

This fixed the problem for me. Reading the source code of Postgres driver, you'll notice that the patch that fixed the alter serial column problem only kicks in when the field is an AutoIncrement field. We would by default think that serial should be AutoIncrement, but it turns out that you have to explicitly declare it so for Postgres driver to realize that.

cc @jinzhu