Long table aliases cause a postgres error even when a short dbName is used

[simlrh]

Describe the Bug

Deeply nested array fields cause the following postgres error:

⨯ [error: table name "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThough" specified more than once] {
  length: 177,
  severity: 'ERROR',
  code: '42712',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '463',
  routine: 'checkNameSpaceConflicts',
  digest: '2925295647'
}

This is because the postgres table names are too long. You can set dbName so that the table itself has a short name, BUT when payload generates a table alias it seems to use the full field path, which is too long.

Drizzle log directly before error:

Query: select "users"."id", "users"."updated_at", "users"."created_at", "users"."email", "users"."reset_password_token", "users"."reset_password_expiration", "users"."salt", "users"."hash", "users"."login_attempts", "users"."lock_until", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."data" as "thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName" from "users" left join lateral (select coalesce(json_agg(json_build_array("users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."_order", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."id", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."nested_field_2", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."data") order by "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."_order" asc), '[]'::json) as "data" from (select * from "shortname" "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName" where "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."_parent_id" = "users"."id" order by "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."_order" asc) "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName" left join lateral (select coalesce(json_agg(json_build_array("users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."_order", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."id", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."data") order by "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."_order" asc), '[]'::json) as "data" from (select * from "short_nested_1" "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1" where "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."_parent_id" = "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName"."id" order by "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."_order" asc) "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1" left join lateral (select coalesce(json_agg(json_build_array("users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."_order", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."id", "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."nested_text") order by "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."_order" asc), '[]'::json) as "data" from (select * from "short_nested_array_1" "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1" where "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."_parent_id" = "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1"."id" order by "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1"."_order" asc) "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1") "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1_nested_array_1" on true) "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName_nested_field_1" on true) "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName" on true order by "users"."created_at" desc limit $1 -- params: [1]

The problem seems to be select * from "shortname" "users_thisIsALongFieldNameThatWillCauseAPostgresErrorEvenThoughWeSetAShorterDBName" where etc

Link to the code that reproduces this issue

https://github.com/simlrh/long-aliases

Reproduction Steps

1. Add your own postgres connection string to .env
2. pnpm dev
3. Try to visit /admin/collections/users

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

db-postgres

Environment Info

Binaries:
  Node: 23.9.0
  npm: 10.9.2
  Yarn: 1.22.22
  pnpm: 10.6.5
Relevant Packages:
  payload: 3.31.0
  next: 15.2.3
  @payloadcms/db-postgres: 3.31.0
  @payloadcms/email-nodemailer: 3.31.0
  @payloadcms/graphql: 3.31.0
  @payloadcms/next/utilities: 3.31.0
  @payloadcms/payload-cloud: 3.31.0
  @payloadcms/richtext-lexical: 3.31.0
  @payloadcms/translations: 3.31.0
  @payloadcms/ui/shared: 3.31.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Sun, 23 Mar 2025 17:17:30 +0000
  Available memory (MB): 63973
  Available CPU cores: 28

[simlrh]

I used an obnoxiously long field name for reproduction, but my real world use case is building a multilingual dictionary following the DMLex data model specification whereby words have meanings have translations have inflected forms have pronunciations have transcriptions and labels, so I got the error table name "entries_senses_headwordTranslation_inflectedForms_pronunciation" specified more than once.

Currently working around by using abbreviated names and descriptive labels.