How to fix Nextval: reached maximum value of sequence in PostgreSQL

Check your application logs or PostgreSQL error logs to find the sequence name mentioned in the error.

-- Example error mentions specific sequence
-- ERROR: nextval: reached maximum value of sequence "users_id_seq"

Note the exact sequence name from the error message.

Connect to your PostgreSQL database and query the sequence information:

SELECT sequencename, data_type, start_value, max_value, last_value
FROM pg_sequences
WHERE sequencename = 'users_id_seq';

If data_type is integer, it maxes out at 2,147,483,647. If it is bigint, it maxes out much higher.

If you can safely reset your IDs (no foreign key references or data dependencies), restart the sequence:

ALTER SEQUENCE users_id_seq RESTART WITH 1;

Warning: Only use this if:
- The table has few rows or old rows can be archived
- No other tables reference these IDs via foreign keys
- You understand the business impact of reusing IDs

For most production systems, this is not safe. Use the long-term fix below.

Alter the sequence to use 64-bit bigint, which has virtually unlimited values:

ALTER SEQUENCE users_id_seq AS bigint;

Then alter the table column to match:

ALTER TABLE users ALTER COLUMN id TYPE bigint USING id::bigint;

Note: If the column is a primary key, this may require dropping and recreating the constraint:

ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ADD PRIMARY KEY (id);

If other tables have foreign keys pointing to this table, update those columns too:

-- Example: orders.user_id references users.id
ALTER TABLE orders ALTER COLUMN user_id TYPE bigint;

Repeat for all columns that reference the affected ID column.

For new tables, never use SERIAL. Instead:

Option A: BIGSERIAL (simpler, but SERIAL pseudo-type is deprecated)

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Option B: IDENTITY (SQL standard, recommended)

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

Identity columns are the modern PostgreSQL approach and offer better control and portability.