Drizzle Team•11mo ago

PostgreSQL "type serial does not exist"

I'm getting an error when using serial and primary key. Just trying out postgres for the first time so its very possible I messed something up, the serial type works perfect on non primary key fields

export const users = pgTable(
  "users",
  {
    id: serial('id').primaryKey(),
    clerk_id: varchar("user_id", { length: 50 }),
    username: varchar("username", { length: 100 }),
  },
  (users) => {
    return {
      username_idx: uniqueIndex("username_idx").on(users.username),
    };
  }
);

export const users = pgTable(
  "users",
  {
    id: serial('id').primaryKey(),
    clerk_id: varchar("user_id", { length: 50 }),
    username: varchar("username", { length: 100 }),
  },
  (users) => {
    return {
      username_idx: uniqueIndex("username_idx").on(users.username),
    };
  }
);

#this is the error displayed when try to push changes
error: type "serial" does not exist

#this is the error displayed when try to push changes
error: type "serial" does not exist

7 Replies

Angelelz•11mo ago

Can you show the SQL migration that got generated?

Fyzz•11mo ago

ALTER TABLE "users" ALTER COLUMN "id" SET DATA TYPE serial;

Angelelz•11mo ago

https://stackoverflow.com/questions/16474720/alter-data-type-of-a-column-to-serial

Stack Overflow

Alter data type of a column to serial

In pgsql, is there a way to have a table of several values, and choose one of them (say, other_id), find out what its highest value is and make every new entry that is put in the table increment from

Angelelz•11mo ago

This is your problem Can't do alter table with serial

Andrii Sherman•11mo ago

we need to add some checks for that I guess I'll put it in backlog

Fyzz•11mo ago

Ahh okay that makes sense because I wiped all tables from my db and then re pushed it, and it worked after that! Thankfully I only had a few tables and had no real data yet

Andrii Sherman•11mo ago

also I suggest to use push only for local development, where you are fine with data truncation for all production cases please use generate command(you are free to modify sql files with migrations if needed)

Gaming

Programming

PostgreSQL "type serial does not exist"