Drizzle Team•12mo ago

Column _ cannot be cast automatically to type integer

I have set a postId column in my comments table, in which I store the ID of the post the comment is attached too. posts.id is of type serial, and I set postId to be an integer, but I keep getting the error column "postId" cannot be cast automatically to type integer with hint You might need to specify "USING "postId"::integer". How can I fix this? Full code:

export const posts = pgTable('post', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('createdAt').defaultNow(),
  title: text('title').notNull(),
  content: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  userId: integer('userId')
    .notNull()
    .references(() => users.id),
  topic: topicsEnum('topics'),
  category: categoriesEnum('categories')
});

export const postRelations = relations(posts, ({ one, many }) => {
  user: one(users, {
    fields: [posts.userId],
    references: [users.id]
  });
  comments: many(comments);
});

export const comments = pgTable('comment', {
  id: serial('id').primaryKey(),
  comment: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  postId: integer('postId')
    .notNull()
    .references(() => posts.id),
  createdAt: timestamp('createdAt').defaultNow()
});

export const commentRelations = relations(comments, ({ one }) => {
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id]
  });
});

export const posts = pgTable('post', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('createdAt').defaultNow(),
  title: text('title').notNull(),
  content: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  userId: integer('userId')
    .notNull()
    .references(() => users.id),
  topic: topicsEnum('topics'),
  category: categoriesEnum('categories')
});

export const postRelations = relations(posts, ({ one, many }) => {
  user: one(users, {
    fields: [posts.userId],
    references: [users.id]
  });
  comments: many(comments);
});

export const comments = pgTable('comment', {
  id: serial('id').primaryKey(),
  comment: text('content'),
  likes: integer('likes').default(0),
  dislikes: integer('dislikes').default(0),
  postId: integer('postId')
    .notNull()
    .references(() => posts.id),
  createdAt: timestamp('createdAt').defaultNow()
});

export const commentRelations = relations(comments, ({ one }) => {
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id]
  });
});

3 Replies

ChrisJ•12mo ago

It seems there's a bug that prevents some column updates: https://github.com/drizzle-team/drizzle-orm/issues/930

GitHub

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

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 u...

ChrisJ•12mo ago

try manually using such code: ALTER TABLE "table" ALTER COLUMN "column" SET DATA TYPE integer USING (id::integer);

gfungOP•12mo ago

Great, it worked - thanks!!

Gaming

Programming

Column _ cannot be cast automatically to type integer