column "summary" cannot be cast automatically to type jsonb

Hello! I'm getting this error when running my migrations, I used to have a text field and now it's a jsonb

summary: jsonb("summary").notNull(),

summary: jsonb("summary").notNull(),

How can I resolve this? This is the migration:

ALTER TABLE "merlin_task_results" ALTER COLUMN "summary" SET DATA TYPE jsonb;--> statement-breakpoint
ALTER TABLE "merlin_task_results" ALTER COLUMN "summary" SET NOT NULL;

ALTER TABLE "merlin_task_results" ALTER COLUMN "summary" SET DATA TYPE jsonb;--> statement-breakpoint
ALTER TABLE "merlin_task_results" ALTER COLUMN "summary" SET NOT NULL;

5 Replies

Angelelz•9mo ago

What's the error?

benjick•9mo ago

Sorry 🤦

error: column "summary" cannot be cast automatically to type jsonb
    at /app/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /app/.next/server/chunks/38.js:7:328
    at async NodePgSession.transaction (/app/.next/server/chunks/38.js:7:40652)
    at async PgDialect.migrate (/app/.next/server/chunks/38.js:7:214)
    at async migrate (/app/.next/server/chunks/38.js:7:42813)
    at async /app/.next/server/chunks/4144.js:1:1007 {
  length: 181,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You might need to specify "USING summary::jsonb".',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '12302',
  routine: 'ATPrepAlterColumnType'
}

error: column "summary" cannot be cast automatically to type jsonb
    at /app/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /app/.next/server/chunks/38.js:7:328
    at async NodePgSession.transaction (/app/.next/server/chunks/38.js:7:40652)
    at async PgDialect.migrate (/app/.next/server/chunks/38.js:7:214)
    at async migrate (/app/.next/server/chunks/38.js:7:42813)
    at async /app/.next/server/chunks/4144.js:1:1007 {
  length: 181,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You might need to specify "USING summary::jsonb".',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '12302',
  routine: 'ATPrepAlterColumnType'
}

Angelelz•9mo ago

Is there data in the table? I would just rename the column, run migration, rename back to what you want and run migration again. Or just edit the SQL migration file and drop the column and recreate it

benjick•9mo ago

Do you think this looks ok?

ALTER TABLE "merlin_task_results" DROP COLUMN "summary";
ALTER TABLE "merlin_task_results" ADD COLUMN "summary" jsonb DEFAULT '{}' NOT NULL;

ALTER TABLE "merlin_task_results" DROP COLUMN "summary";
ALTER TABLE "merlin_task_results" ADD COLUMN "summary" jsonb DEFAULT '{}' NOT NULL;

Do I need this? --> statement-breakpoint

Angelelz•9mo ago

that seems to to be what you need You don't really need the comment

Gaming

Programming

column "summary" cannot be cast automatically to type jsonb