[Regression] Index Names Too Long in versions tables

[christophemenager]

Describe the Bug

Problem

PayloadCMS generates PostgreSQL index names that are excessively long and contain duplicate field name segments, causing database errors when the index name exceeds PostgreSQL's identifier length limit.

The automatic index generation creates names like:
_scenario_1_v_version_hideout_camera1_version_hideout_camera1_time2_image_idx
The segmentversion_hideout_camera1 is duplicated

Expected solution

_scenario_1_v_hideout_camera1_time2_image_idx should be the index name

Error details

⨯ [Error: Failed query: CREATE INDEX "_scenario_1_v_version_hideout_camera1_version_hideout_camera1_time2_image_idx" ON "cms"."_scenario_1_v" USING btree ("version_hideout_camera1_time2_image_id"); params: ] {
  query: 'CREATE INDEX "_scenario_1_v_version_hideout_camera1_version_hideout_camera1_time2_image_idx" ON "cms"."_scenario_1_v" USING btree ("version_hideout_camera1_time2_image_id");',
  params: [],
  digest: '2964061673',
  [cause]: [error: relation "_scenario_1_v_version_hideout_camera1_version_hideout_camera1_t" already exists] {
    length: 145,
    severity: 'ERROR',
    code: '42P07',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'index.c',
    line: '873',
    routine: 'index_create'
  }
}

Link to the code that reproduces this issue

/

Reproduction Steps

1. Create a collection with versioning enabled (draft: true)
2. Make sure to have deeply nested fields with a tab field

{
  "slug": "scenario-1",
  "admin": {
    "useAsTitle": "title"
  },
  "versions": {
    "drafts": {
      "autosave": true
    }
  },
  "fields": [
    {
      "type": "tabs",
      "tabs": [
        {
          "name": "hideout",
          "fields": [
            {
              "label": "Cameras",
              "type": "tabs",
              "unique": true,
              "tabs": [
                {
                  "name": "camera1",
                  "fields": [
                    {
                      "type": "row",
                      "fields": [
                        {
                          "name": "time1Image",
                          "type": "upload",
                          "relationTo": "scenario-1-image-media",
                          "required": true,
                          "unique": true
                        }
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

Binaries:
  Node: 22.16.0
  npm: 10.9.2
  Yarn: N/A
  pnpm: 10.11.0
Relevant Packages:
  payload: 3.47.0
  next: 15.4.1
  @payloadcms/db-postgres: 3.47.0
  @payloadcms/email-resend: 3.47.0
  @payloadcms/graphql: 3.47.0
  @payloadcms/next/utilities: 3.47.0
  @payloadcms/plugin-cloud-storage: 3.47.0
  @payloadcms/richtext-lexical: 3.47.0
  @payloadcms/storage-s3: 3.47.0
  @payloadcms/translations: 3.47.0
  @payloadcms/ui/shared: 3.47.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 24.5.0: Tue Apr 22 19:53:27 PDT 2025; root:xnu-11417.121.6~2/RELEASE_ARM64_T6041
  Available memory (MB): 65536
  Available CPU cores: 14

[github-actions]

Please add a reproduction in order for us to be able to investigate.

Depending on the quality of reproduction steps, this issue may be closed if no reproduction is provided.

Why was this issue marked with the invalid-reproduction label?

To be able to investigate, we need access to a reproduction to identify what triggered the issue. We prefer a link to a public GitHub repository created with create-payload-app@latest -t blank or a forked/branched version of this repository with tests added (more info in the reproduction-guide).

To make sure the issue is resolved as quickly as possible, please make sure that the reproduction is as minimal as possible. This means that you should remove unnecessary code, files, and dependencies that do not contribute to the issue. Ensure your reproduction does not depend on secrets, 3rd party registries, private dependencies, or any other data that cannot be made public. Avoid a reproduction including a whole monorepo (unless relevant to the issue). The easier it is to reproduce the issue, the quicker we can help.

Please test your reproduction against the latest version of Payload to make sure your issue has not already been fixed.

I added a link, why was it still marked?

Ensure the link is pointing to a codebase that is accessible (e.g. not a private repository). "example.com", "n/a", "will add later", etc. are not acceptable links -- we need to see a public codebase. See the above section for accepted links.

Useful Resources

• Reproduction Guide
• Contributing to Payload

[christophemenager]

Note: removing unique: true, and adding index: false on the impacted field does not remove the error, I guess it's because the error is related to the versions table and not the original table

[thomas-coldwell]

Also can confirm this error updating from 3.40.0 to 3.48.0

[christophemenager]

It's indeed a regression that occured probably around 3.41 or 3.42

[omaroubari]

also seeing this error

[tori-takashi]

Me too, I just faced with this error.
dbName is just rename table name, we also need rename option about index name.

[christophemenager]

@r1tsuu I found 2 “potentially” related changes to the db-postgres package in the relevant timeframe:

• fix(db-postgres): reordering of enum values, bump drizzle-kit@0.31.0 and drizzle-orm@0.43.1 #12256 => the drizzle API to create indexes changed (https://orm.drizzle.team/docs/latest-releases/drizzle-orm-v0310), which may have broken something?
• fix(db-postgres): x3 and more nested blocks regression #12770 => change related to nested fields, but not sure if it's related

[Bjornnyborg]

I also ran into this when upgrading a project from 3.36 -> 3.48
Downgraded to 3.40 fixed it, until this issue has been solved. 😊

[christophemenager]

@denolfe sorry to ping you directly but could you confirm you have this issue under your radar? Could someone at your team triage it and assign it to the right team?
This is a regression confirmed by multiple users since I created it 3 weeks ago.
It's preventing some users to upgrade.