使用PostgreSQL 15.8时Prisma迁移报'type "serial" does not exist'错误求助

问题背景 #

在Prisma项目执行迁移操作时，遇到错误提示type "serial" does not exist，但使用的是支持serial语法的PostgreSQL 15.8版本。已完成以下排查：

• 确认使用最新版Prisma Client（@prisma/client: "5.11.0"）
• 验证PostgreSQL版本为15.8
• 删除node_modules和prisma目录清理缓存后重装依赖
• 在PostgreSQL控制台手动执行SQL迁移，仍报错
• 确认仅安装了plpgsql扩展
• 执行npx prisma migrate reset后错误依旧

迁移文件内容：

-- AlterTable
ALTER TABLE "ClosedPositions" DROP CONSTRAINT "ClosedPositions_pkey",
ALTER COLUMN "id" SET DATA TYPE SERIAL,
ADD CONSTRAINT "ClosedPositions_pkey" PRIMARY KEY ("id");

-- AlterTable
ALTER TABLE "OpenPositions" DROP CONSTRAINT "OpenPositions_pkey",
ALTER COLUMN "id" SET DATA TYPE SERIAL,
ADD CONSTRAINT "OpenPositions_pkey" PRIMARY KEY ("id");

迁移时错误日志：

Error: P3006

Migration `20240920182327_change_id_to_int_in_positions` failed to apply cleanly to the shadow database. 
Error:
ERROR: type "serial" does not exist
   0: sql_schema_connector::validate_migrations
           with namespaces=None
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:325
   1: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:276

执行npx prisma migrate reset后的错误：

Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20240920182327_change_id_to_int_in_positions

Database error code: 42704

Database error:
ERROR: type "serial" does not exist

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42704), message: "type \"serial\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_type.c"), line: Some(274), routine: Some("typenameType") }

错误原因 #

PostgreSQL中的SERIAL并不是真实的数据类型，只是一种语法糖：它会自动创建序列对象、将列类型设为integer、并把列的默认值设为序列的nextval()结果。而ALTER COLUMN ... SET DATA TYPE仅支持指定PostgreSQL原生存在的数据类型（比如integer），直接写SERIAL会被数据库判定为不存在的类型，因此报错。

该问题与Prisma配置无关，属于迁移SQL的写法错误，所以手动执行SQL也会失败。

解决步骤 #

方法1：修改现有迁移文件 #

将原迁移文件中ALTER COLUMN "id" SET DATA TYPE SERIAL的逻辑替换为符合PostgreSQL规范的操作（以ClosedPositions表为例）：

-- AlterTable for ClosedPositions
ALTER TABLE "ClosedPositions" DROP CONSTRAINT "ClosedPositions_pkey";
-- 创建关联序列
CREATE SEQUENCE IF NOT EXISTS "ClosedPositions_id_seq";
-- 修改列类型为integer
ALTER COLUMN "id" SET DATA TYPE integer;
-- 设置列默认值为序列的自增值
ALTER COLUMN "id" SET DEFAULT nextval('"ClosedPositions_id_seq"');
-- 关联序列与列，删除表时自动删除序列
ALTER SEQUENCE "ClosedPositions_id_seq" OWNED BY "ClosedPositions"."id";
-- 重新添加主键约束
ADD CONSTRAINT "ClosedPositions_pkey" PRIMARY KEY ("id");

-- AlterTable for OpenPositions
ALTER TABLE "OpenPositions" DROP CONSTRAINT "OpenPositions_pkey";
CREATE SEQUENCE IF NOT EXISTS "OpenPositions_id_seq";
ALTER COLUMN "id" SET DATA TYPE integer;
ALTER COLUMN "id" SET DEFAULT nextval('"OpenPositions_id_seq"');
ALTER SEQUENCE "OpenPositions_id_seq" OWNED BY "OpenPositions"."id";
ADD CONSTRAINT "OpenPositions_pkey" PRIMARY KEY ("id");

方法2：通过Prisma Schema自动生成正确迁移 #

1. 在schema.prisma中定义自增主键字段：

model ClosedPositions {
  id    Int     @id @default(autoincrement())
  // 其他字段...
}

model OpenPositions {
  id    Int     @id @default(autoincrement())
  // 其他字段...
}

2. 执行npx prisma migrate dev，让Prisma自动生成符合规范的迁移文件，替换手动编写的错误文件。

验证操作 #

修改完成后，执行npx prisma migrate dev或npx prisma migrate reset，确认迁移成功。

内容的提问来源于stack exchange，提问作者seven