[BUG]: error: column "role" cannot be cast automatically to type user_role

[ShiftyMcCool]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.10

Describe the Bug

I created a table in a previous migration with a field set for text. Later, I updated that field to an enum and generated the SQL.

Schema (bits surrounded with ** were added to existing schema):

**export const userRole = pgEnum('user_role', ['ACCOUNT_ADMIN'])**

export const users = pgTable(
  'users',
  {
    id: uuid('id').defaultRandom().primaryKey().notNull(),
    crmId: text('crm_id'),
    authId: text('auth_id'),
    accountId: text('account_id')
      .notNull()
      .references(() => accounts.crmId),
    firstName: text('first_name').notNull(),
    lastName: text('last_name').notNull(),
    email: text('email').notNull(),
    role: **userRole**('role').notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
    deletedAt: timestamp('deleted_at'),
  },
  (users) => ({
    userId: uniqueIndex().on(users.id),
    userEmail: uniqueIndex().on(users.email),
    crmId: uniqueIndex().on(users.crmId),
    authId: uniqueIndex().on(users.authId),
  }),
)

Generated SQL:

DO $$ BEGIN
 CREATE TYPE "user_role" AS ENUM('ACCOUNT_ADMIN');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
ALTER TABLE "users" ALTER COLUMN "role" SET DATA TYPE user_role;

Received this error when attempting to run the generated SQL with the runMigration() method:

error: column "role" cannot be cast automatically to type user_role
    at /Users/ryan/Code/sdes/sdes-api/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /Users/ryan/Code/sdes/sdes-api/node_modules/src/pg-core/dialect.ts:74:7
    at async NodePgSession.transaction (/Users/ryan/Code/sdes/sdes-api/node_modules/src/node-postgres/session.ts:138:19)
    at async PgDialect.migrate (/Users/ryan/Code/sdes/sdes-api/node_modules/src/pg-core/dialect.ts:67:3)
    at async migrate (/Users/ryan/Code/sdes/sdes-api/node_modules/src/node-postgres/migrator.ts:10:2)
    at async ConsoleService.migrate (/Users/ryan/Code/sdes/sdes-api/src/console/console.service.ts:23:5) {
  length: 183,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You might need to specify "USING role::user_role".',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '11714',
  routine: 'ATPrepAlterColumnType'
}

Needed to change the following to allow it to run: ALTER TABLE "users" ALTER COLUMN "role" SET DATA TYPE user_role **USING (role::user_role)**;

Expected behavior

Generated SQL should cast enums correctly

Environment & setup

No response

[ShiftyMcCool]

Also just happened with changing a text type to a uuid so not just an enum issue

[boian-ivanov]

Is this still being looked at? Seems to still be happening when changing a column's type.

[jjim1982]

Also experienced trying to alter column from integer to boolean using generate / pushpg

Got around it using ALTER TABLE "users" ALTER COLUMN "admin" SET DATA TYPE boolean USING (admin::boolean);

[RakibulBh]

Also experienced trying to alter column from integer to boolean using generate / pushpg

Got around it using ALTER TABLE "users" ALTER COLUMN "admin" SET DATA TYPE boolean USING (admin::boolean);

Where do i use that?

[tomparkp]

Just noting that this seems to come up pretty frequently when using Payload - essentially anytime you rename something like a radio field, select, etc. and db push (which is automatic with Payload in development mode) things end up in a bad state with a cannot be cast automatically error.

[jackkrone]

Also experienced trying to alter column from integer to boolean using generate / pushpg Got around it using ALTER TABLE "users" ALTER COLUMN "admin" SET DATA TYPE boolean USING (admin::boolean);

Where do i use that?

In the latest sql file that drizzle kit generated. You just add the last part USING (admin::boolean) to the end of the line ALTER TABLE "users" ALTER COLUMN "admin" SET DATA TYPE boolean

[jackkrone]

Just found this comment which explains why the error occurs. If you want to avoid editing sql files then first delete the column and generate a migration, then re-add your edits and generate another migration. https://stackoverflow.com/a/31592810/16335768