fix(postgres): correct changeColumn for enum arrays and enum defaults - #18361

Two independent, pre-existing bugs in the PostgreSQL changeColumnQuery. Both were found while reviewing #18254 and are unrelated to that PR's column-comment work.

1. ARRAY(ENUM) was always rejected

The TYPE clause used the array type while the USING clause cast to the scalar enum type, so PostgreSQL refused every such change.

await queryInterface.changeColumn('users', 'tags', {
  type: DataTypes.ARRAY(DataTypes.ENUM('x', 'y')),
});

Before:

ALTER TABLE "users" ALTER COLUMN "tags" TYPE "public"."enum_users_tags"[] USING ("tags"::"public"."enum_users_tags");
-- result of USING clause for column "tags" cannot be cast automatically to type enum_users_tags[]

After, the cast targets the array type. I checked in psql that a direct ::enum[] cast works from varchar, text[] and varchar[], so no intermediate cast is needed.

2. Changing a column to an ENUM with a defaultValue was always rejected

SET DEFAULT was emitted before ALTER COLUMN ... TYPE, so when the type change ran the column still carried a default typed as the old type.

await queryInterface.changeColumn('users', 'status', {
  type: DataTypes.ENUM('pending', 'complete'),
  defaultValue: 'pending',
});
-- default for column "status" cannot be cast automatically to type enum_users_status

The same change without defaultValue already worked, so it was purely statement order. Now the old default is dropped, the type is changed, and the new default is set afterwards.

Tests

Two integration tests and two unit fixtures. The unit fixtures matter because no existing changeColumnQuery fixture covered a DEFAULT at all, which is why the reordering broke nothing.

The array test is guarded by dialect.supports.dataTypes.ARRAY rather than a dialect name. The default test needs no guard and now runs on every dialect that reaches the surrounding enum block; verified passing on PostgreSQL and SQLite.

Verified locally against PostgreSQL 17: full PostgreSQL unit suite (2836), query-interface integration (84), and model sync (25).

Note for sequencing: this touches changeColumnQuery, as does #18254, so whichever lands second will need a small rebase.

Related work

One of a group of PRs that came out of reviewing #18254. Listed so reviewers can see the relationship.

How they interact:

• fix(postgres): keep USING clause out of COMMENT ON COLUMN when altering ENUM #18254 and fix(postgres): correct changeColumn for enum arrays and enum defaults #18361 both edit changeColumnQuery in packages/postgres/src/query-generator.js. No semantic conflict, but whichever merges second needs a small rebase.
• fix(postgres): correct changeColumn for enum arrays and enum defaults #18361 and fix(postgres): support default values on ARRAY(ENUM) columns #18364 are both required for changeColumn to an ARRAY(ENUM) with a defaultValue to work end to end. All four combinations were checked: fix(postgres): support default values on ARRAY(ENUM) columns #18364 alone generates correct SQL but fails on the cast, fix(postgres): correct changeColumn for enum arrays and enum defaults #18361 alone still throws on the enum name, together they pass.
• fix(postgres): correct changeColumn for enum arrays and enum defaults #18361 and fix(mssql,oracle): keep defaultValue on enum and boolean columns #18365 collide directly. Both add a test called properly generates alter queries for enums with a default value to packages/core/test/unit/sql/change-column.test.js, asserting different output. fix(mssql,oracle): keep defaultValue on enum and boolean columns #18365 is branched off main, so its PostgreSQL expectation also encodes the SET DEFAULT ordering that fix(postgres): correct changeColumn for enum arrays and enum defaults #18361 fixes. Whichever merges second must delete the stale test and correct the PostgreSQL, MSSQL and Oracle expectations.
• PostgreSQL column queries parse generated SQL instead of using the attribute object #18362 tracks the underlying structural problem these all work around: the PostgreSQL generators reconstruct column definitions by parsing generated SQL strings rather than reading the attribute object. No direction is decided there.

🤖 Generated with Claude Code

Summary by CodeRabbit

• Bug Fixes
  • Improved PostgreSQL column changes involving ENUM types and default values.
  • Corrected SQL generation for ENUM array columns, including proper array casting.
  • Fixed handling of UNIQUE constraints when modifying ENUM columns.
  • Improved refreshing and removal of column defaults during column changes.
  • Added support for changing columns to ENUM types with defaults across supported database dialects.