Error: column "XYZ" cannot be cast automatically to type enum_x_y_z

[tilman]

Describe the Bug

If I change the options of a select field I will get the error Error: column "XYZ" cannot be cast automatically to type enum_x_y_z.
Under the hood the error is caused by the following SQL command: ALTER TABLE xyz ALTER COLUMN x_y_z SET DATA TYPE enum_x_y_z;

The solution would be to run the SQL command with USING enum_xyz appended. This will enable automatic conversion using this new datatype. Therefore the query should be changed to ALTER TABLE xyz ALTER COLUMN x_y_z SET DATA TYPE enum_x_y_z USING xyz::enum_xyz;

Link to the code that reproduces this issue

N/A

Reproduction Steps

1. Start a new payload project npx create-payload-app@beta -t blank
2. create a field with type select
3. add some content with the field
4. change the options of the filed
5. you will receive the cast error

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

db-postgres, db-vercel-postgres

Environment Info

Binaries:
Node: 20.18.0
npm: 10.8.2
Yarn: 1.22.21
pnpm: 8.15.8
Relevant Packages:
payload: 3.0.0-beta.116
next: 15.0.0-canary.173
@payloadcms/db-postgres: 3.0.0-beta.116
@payloadcms/email-nodemailer: 3.0.0-beta.116
@payloadcms/graphql: 3.0.0-beta.116
@payloadcms/live-preview: 3.0.0-beta.116
@payloadcms/live-preview-react: 3.0.0-beta.116
@payloadcms/next/utilities: 3.0.0-beta.116
@payloadcms/plugin-cloud: 3.0.0-beta.116
@payloadcms/plugin-form-builder: 3.0.0-beta.116
@payloadcms/plugin-nested-docs: 3.0.0-beta.116
@payloadcms/plugin-redirects: 3.0.0-beta.116
@payloadcms/plugin-search: 3.0.0-beta.116
@payloadcms/plugin-seo: 3.0.0-beta.116
@payloadcms/richtext-lexical: 3.0.0-beta.116
@payloadcms/translations: 3.0.0-beta.116
@payloadcms/ui/shared: 3.0.0-beta.116
react: 19.0.0-rc-3edc000d-20240926
react-dom: 19.0.0-rc-3edc000d-20240926
Operating System:
Platform: darwin
Arch: arm64
Version: Darwin Kernel Version 22.6.0: Wed Jul 5 22:22:05 PDT 2023; root:xnu-8796.141.3~6/RELEASE_ARM64_T6000
Available memory (MB): 32768
Available CPU cores: 10

[jmikrut]

Hey @tilman —

We wouldn't be able to control the output SQL statements from the Payload side - that's all handled by Drizzle.

I'd try and create a minimal reproducible demo for the Drizzle team and open an issue there.

[github-actions]

This issue has been automatically locked.
Please open a new issue if this issue persists with any additional detail.