Postgres Sequence Reset Generator

Bulk imports, COPY and pg_restore write ids directly and leave serial and identity sequences behind, so the next INSERT fails with duplicate key value violates unique constraint. Paste your CREATE TABLE statements (or pg_dump output), or just list schema.table.column, and get correct setval() SQL that moves each sequence to MAX(id) + 1 - with empty tables starting at 1, quoted mixed-case names, shared sequences and identity columns handled. You can also generate a single DO block that fixes every sequence in a schema and a diagnostic query that lists the sequences that are behind. Everything runs in your browser.

InputOutputTarget

Default schema

Wrap in BEGIN / COMMITLOCK tables (block INSERTs)Identity: add ALTER ... RESTART formAlways quote identifiers

Samples:

CREATE TABLE DDL or schema.table.column list

-- Restored from a pg_dump / CSV bulk load with explicit ids
CREATE TABLE public.customers (
    id          bigserial PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    note        text DEFAULT 'imported (legacy), see ticket #42',
    created_at  timestamptz NOT NULL DEFAULT now()
);

/* identity column with a custom START */
CREATE TABLE IF NOT EXISTS sales.orders (
    order_id     integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1),
    customer_id  bigint NOT NULL REFERENCES public.customers (id),
    status       varchar(20) CHECK (status IN ('new', 'paid', 'shipped')),
    total        numeric(12, 2) DEFAULT 0.00
);

-- quoted mixed-case names are case sensitive
CREATE TABLE sales."OrderItems" (
    "ItemID"   serial,
    "Order ID" integer NOT NULL,
    sku        text,
    CONSTRAINT "OrderItems_pkey" PRIMARY KEY ("ItemID")
);

-- pg_dump style: plain integer column + separate sequence and default
CREATE TABLE public.audit_log (
    id      integer NOT NULL,
    payload jsonb DEFAULT '{"source": "import; batch (1)"}'::jsonb
);
CREATE SEQUENCE public.audit_log_id_seq AS integer START WITH 1 INCREMENT BY 1;
ALTER TABLE ONLY public.audit_log ALTER COLUMN id SET DEFAULT nextval('public.audit_log_id_seq'::regclass);

-- no sequence here: skipped
CREATE TABLE public.settings (key text PRIMARY KEY, value text);

4 sequence-backed columns found

Table	Column	Source
public.customers	id	bigserial
sales.orders	order_id	identity (start 1000)
sales."OrderItems"	"ItemID"	serial
public.audit_log	id	nextval('public.audit_log_id_seq')

No sequence-backed column in: public.settings.

Generated setval SQL

BEGIN;

-- public.customers.id (bigserial)
SELECT setval(pg_get_serial_sequence('public.customers', 'id'), COALESCE(MAX(id), 0) + 1, false) FROM public.customers;

-- sales.orders.order_id (identity)
SELECT setval(pg_get_serial_sequence('sales.orders', 'order_id'), COALESCE(MAX(order_id) + 1, 1000), false) FROM sales.orders;

-- sales."OrderItems"."ItemID" (serial)
SELECT setval(pg_get_serial_sequence('sales."OrderItems"', 'ItemID'), COALESCE(MAX("ItemID"), 0) + 1, false) FROM sales."OrderItems";

-- public.audit_log.id (nextval('public.audit_log_id_seq'))
SELECT setval('public.audit_log_id_seq', COALESCE(MAX(id), 0) + 1, false) FROM public.audit_log;

COMMIT;

Do more than postgres sequence reset generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

Why Postgres sequences fall behind MAX(id)

A serial, bigserial or identity column only calls nextval() when the INSERT leaves the column out. Any load that writes the id itself - COPY from a CSV export, INSERT statements with explicit ids, a data-only pg_restore, logical replication, or a migration tool - fills the table without touching the sequence. The sequence still says 1 (or whatever it said before), so the first normal INSERT asks for an id that already exists and fails with ERROR: duplicate key value violates unique constraint "orders_pkey" - DETAIL: Key (id)=(1) already exists. It then fails again for id 2, 3 and so on, because every failed attempt still consumes a value.

setval and the is_called flag

setval(seq, n) is short for setval(seq, n, true): it marks n as already used, so the next nextval() returns n + 1. The popular one-liner SELECT setval(seq, MAX(id)) FROM t therefore works for tables with rows but breaks on an empty table, where MAX(id) is NULL (setval returns NULL and does nothing) or, with COALESCE(MAX(id), 0), fails because 0 is below the sequence's MINVALUE of 1. The generator uses setval(seq, COALESCE(MAX(id), 0) + 1, false) instead: is_called = false means the next nextval() returns exactly that value, which is MAX + 1 for a filled table and 1 for an empty one.

serial, identity and nextval() defaults

serial and bigserial create a sequence named table_column_seq that is OWNED BY the column, and GENERATED AS IDENTITY columns own an internal sequence too, so pg_get_serial_sequence('schema.table', 'column') finds both. Its first argument is parsed like SQL (mixed case must be written as '"Sales"."OrderItems"'), while the second is taken literally ('ItemID', not '"ItemID"'). A column that only has DEFAULT nextval('some_seq') without OWNED BY is invisible to pg_get_serial_sequence, so the generator uses the sequence name from the default directly, and the DO block follows the dependency from pg_attrdef. When several tables share one sequence, all of them are combined with GREATEST so one table does not rewind the sequence below another's ids.

Running the fix safely

Sequence changes are not transactional - a ROLLBACK does not undo setval - but wrapping the fix in BEGIN ... COMMIT together with LOCK TABLE ... IN EXCLUSIVE MODE still matters on a live system: the lock blocks INSERTs (while allowing reads) between reading MAX(id) and moving the sequence, so no session can grab a stale id in between. Run the diagnostic query first to see which sequences are behind, run the fix, then run the diagnostic again: every row should say ok.

How to use

Paste CREATE TABLE / ALTER TABLE DDL (serial, bigserial, GENERATED AS IDENTITY and DEFAULT nextval() columns are detected), or one schema.table.column per line. Load a sample to see both formats.
Choose the output: per-column setval statements, one DO block that fixes every sequence in the listed schemas, or a read-only diagnostic query. Optionally wrap it in a transaction, lock the tables against concurrent INSERTs, or never move a sequence backwards.
Run the diagnostic query to see which sequences are behind, run the generated fix, then run the diagnostic again - every row should report ok and INSERTs stop failing with duplicate key errors.

Frequently asked questions

How do I fix "duplicate key value violates unique constraint" on a serial or identity column?

The sequence behind the column is lower than the ids already in the table, usually after an import that supplied explicit ids. Move it past the current maximum: SELECT setval(pg_get_serial_sequence('public.orders', 'id'), COALESCE(MAX(id), 0) + 1, false) FROM public.orders; The next INSERT then gets MAX(id) + 1. pg_get_serial_sequence works for serial, bigserial and GENERATED AS IDENTITY columns; for a column whose default is nextval('some_seq') without OWNED BY, pass the sequence name to setval directly.

Why use setval with false instead of SELECT setval(seq, MAX(id))?

The third argument is is_called. With true (the default) the value you pass counts as already used and the next nextval() returns value + 1; with false the next nextval() returns exactly the value. setval(seq, MAX(id)) returns NULL and changes nothing on an empty table, and setval(seq, COALESCE(MAX(id), 0)) fails with value 0 is out of bounds because the minimum is 1. setval(seq, COALESCE(MAX(id), 0) + 1, false) is correct in both cases: MAX + 1 for a filled table and 1 for an empty one.

How do I reset all sequences in a Postgres schema at once?

Use the DO block output. It reads pg_depend to find every sequence owned by a serial or identity column, plus sequences referenced only by a DEFAULT nextval(), computes the MAX of every column that uses each sequence, and calls setval for each one inside a single transaction, printing a NOTICE per sequence. Run the diagnostic query before and after to confirm. To browse the tables, check ids and run the script against a live database, Chat2DB is a free SQL client with an AI assistant - download it at https://chat2db.ai/download or use https://app.chat2db.ai.