Gorm Postgres: clean or existing database affects whether WHERE clause affects index

GORM Playground Link

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

Description

Hello! I'm using gorm in a postgres web application. Recently, I ran into an unexpected behavior that I have reproduced in the go-gorm/playground framing (but it is a little unnatural). The unexpected behavior is differences between migrations between the database being in a clean state and it already has the relevant table. For more complicated migrations, I would not find this surprising, but to me this change seems like a purely additive migration, so I would expect migrating from a clean database and migrating from an existing table to be identical. I will describe the situation below, then how I translated it into the gorm playground PR.

The (simplified) Situation

I have a User struct that has an Address:

import (
    "gorm.io/gorm"
)

type Address struct {
    gorm.Model
    UserId uint
}

// User has one address.
type User struct {
    gorm.Model
    Name    string
    Address Address `gorm:"foreignKey:user_id"`
}

I've migrated this data onto my database. Only, whoopsie, my mistake, this allows for multiple Addresses to point to the same User through the UserId field. So, I want to add a unique constraint, but I soft-delete the duplicates first so the uniqueness constraint doesn't fail and add a where clause that the row isn't soft-deleted:

import (
    "gorm.io/gorm"
)

type Address struct {
    gorm.Model
    UserId      uint `gorm:"uniqueIndex:,where: deleted_at is null"`
}

// User has one address.
type User struct {
    gorm.Model
    Name    string
    Address Address `gorm:"foreignKey:user_id"`
}

Running the migration from an already migrated database leads to the where: deleted_at is null not translating to a WHERE condition on the index. Compare this to running from a clean database, where the condition makes it.

Migrating from a clean database yields:

gorm=# \d addresses;
                                        Table "public.addresses"
    Column    |           Type           | Collation | Nullable |                Default
--------------+--------------------------+-----------+----------+---------------------------------------
 id           | bigint                   |           | not null | nextval('addresses_id_seq'::regclass)
 created_at   | timestamp with time zone |           |          |
 updated_at   | timestamp with time zone |           |          |
 deleted_at   | timestamp with time zone |           |          |
 user_id      | bigint                   |           |          |
 second_field | text                     |           |          |
Indexes:
    "addresses_pkey" PRIMARY KEY, btree (id)
    "idx_addresses_deleted_at" btree (deleted_at)
    "idx_addresses_user_id" UNIQUE, btree (user_id) WHERE deleted_at IS NULL
Foreign-key constraints:
    "fk_users_address" FOREIGN KEY (user_id) REFERENCES users(id)

Whereas migrating from a database that already has the first version of Address leads to:

gorm=# \d addresses;
                                        Table "public.addresses"
    Column    |           Type           | Collation | Nullable |                Default
--------------+--------------------------+-----------+----------+---------------------------------------
 id           | bigint                   |           | not null | nextval('addresses_id_seq'::regclass)
 created_at   | timestamp with time zone |           |          |
 updated_at   | timestamp with time zone |           |          |
 deleted_at   | timestamp with time zone |           |          |
 user_id      | bigint                   |           |          |
 second_field | text                     |           |          |
Indexes:
    "addresses_pkey" PRIMARY KEY, btree (id)
    "idx_addresses_deleted_at" btree (deleted_at)
    "idx_addresses_user_id" UNIQUE CONSTRAINT, btree (user_id)
Foreign-key constraints:
    "fk_users_address" FOREIGN KEY (user_id) REFERENCES users(id)

Note that the WHERE clause on the first output's idx_addresses_user_id index does not exist on the second.

This is the gist of the issue: when I ran into local database issues, I ended up dropping this table which lead to the uniqueness constraint having the proper WHERE clause (per the first output). In production, however, I experienced behavior similar to the second output, which did not have the WHERE clause, so the migration failed to apply due to the "duplicates" since it didn't take soft-deletion into account.

The Reproduction

There are two things I want to model in the reproduction: running the migration from an existing or clean database. I did this across two different commits in the linked go-orm/playground issue: HEAD and https://github.com/go-gorm/playground/pull/627/commits/c05d2938533e78b91a1bfaa8dfb64fc10315dc45. HEAD starts from an empty database, migrates to the desired final state, and then we can describe the address table from that. c05d2938533e78b91a1bfaa8dfb64fc10315dc45, by comparison, starts from an empty database, migrates to the intermediate state, then migrates to the desired final state. Describing the address table from that, we see that there is a difference between the two.

Note that to mimic the multiple migration steps, I use two structures, Address which represents the first struct without the gorm uniqueness constraint, and Address2, which represents the struct with the uniqueness constraint. So that migrations affect the same table, I provide a table name method so both of them affect the addresses table.

This issue is postgres specific. I'm running GORM_DIALECT=postgres ./test.sh to apply migrations to the postgres instance run through docker-desktop.

Comment From: klipach

Faced with the same issue in v1.25.2 in v1.24.3 everything works as expected