I'm trying to run a migration in my Prisma project, but I'm encountering the error "type "serial" does not exist", even though I'm using PostgreSQL 15.8, which should support this data type.
Steps I've taken:
I've ensured that I'm using the latest version of Prisma Client (@prisma/client: "5.11.0"). I've checked my PostgreSQL version (psql -V), which is PostgreSQL 15.8. I've cleared the Prisma cache by deleting the node_modules and prisma directories and then reinstalling dependencies (npm install). I've tried manually executing the SQL migration in the PostgreSQL console, but I still encounter the error. I don't have any additional PostgreSQL extensions installed (\dx only shows plpgsql). My migration file:
-- 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
What could be causing this error, and how can I resolve it? Is there a conflict or configuration issue that's preventing Prisma from correctly recognizing the serial data type?
I've also tried
npx prisma migrate reset
and I get error:
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") }
many thanks for any help
The issue is serial is not a type it is essentially a macro as described in the link:
The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:
CREATE TABLE tablename ( colname SERIAL );
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq AS integer; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
The error is coming from Postgres itself:
create table id_test(id integer);
alter table id_test alter column id type serial;
ERROR: type "serial" does not exist
If you want to add an autoincremented default to the column then from here Alter Table:
ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
Which using my previous example would be:
alter table id_test alter column id set not null, alter column id add generated always as identity;
The set not null
is required for an identity column.