⏭

PostgreSQL Sequence Functions

Complete reference for PostgreSQL sequence functions covering nextval(), currval(), setval(), and lastval() for managing serial and identity column sequences. Includes sequence creation, gap behaviour, and multi-session concurrency notes. Updated for PostgreSQL 16.

5 functions

Jump to function

nextval currval lastval setval pg_get_serial_sequence

What are PostgreSQL Sequence Functions?

PostgreSQL sequence functions generate unique integer values, used internally by SERIAL and IDENTITY columns for primary key generation. nextval() advances the sequence and returns the next value, currval() returns the last value used in the current session, and setval() manually resets a sequence to a specified value. Sequences are not rolled back on transaction rollback, so gaps in the sequence are expected and normal.

nextval

PG 7.4+→ bigint

Advances the sequence and returns the new value. The sequence is updated even if the transaction is rolled back.

DeveloperDBA

Signature

nextval ( regclass ) → bigint

Parameters

Parameter	Type	Description
sequence_name	regclass (text or OID)	Name of the sequence (quoted if needed)

Examples

sql

SELECT nextval('my_seq');

→1 (or next value)

sql

INSERT INTO orders (id, ...) VALUES (nextval('order_id_seq'), ...);

→Inserts with next sequence value

sql

-- Preallocate a batch of IDs for bulk inserts
SELECT nextval('item_seq') FROM generate_series(1, 10);

→10 consecutive sequence values (may have gaps from concurrent sessions)

sql

-- Check what value will come next without advancing the sequence
SELECT last_value + increment_by FROM pg_sequences WHERE sequencename = 'my_seq';

→Predicted next value without consuming it

⚠Anti-Pattern— Assuming no gaps in sequence values after rollbacks

Sequences are non-transactional by design. Every `nextval` call permanently advances the counter, even inside a transaction that is later rolled back, aborted due to an error, or part of a connection that drops mid-flight. Applications that treat sequence values as a dense, gap-free range — for example, using them as invoice numbers for auditors — will occasionally discover missing numbers and have no explanation for them.

✓ Instead: Accept that gaps are normal and inevitable. If you need a truly gap-free, auditable number (e.g., legal invoice numbering), maintain a separate counter table inside the same transaction as the business record, using `SELECT ... FOR UPDATE` to serialize access. For most cases, just document to stakeholders that gaps in sequence-backed IDs are expected behaviour, not data loss.

`nextval` always advances the sequence, even if the calling transaction is rolled back. This is intentional — it prevents sequence deadlocks. Don't assume your sequence values will be gapless.

example

SELECT nextval('order_id_seq'); -- This sequence value is consumed even if your transaction fails

→Sequence gaps are normal and expected

currval

PG 7.4+→ bigint

Returns the most recent value returned by nextval for the given sequence in the current session. Raises an error if nextval has not been called in this session.

DeveloperDBA

Signature

currval ( regclass ) → bigint

Parameters

Parameter	Type	Description
sequence_name	regclass	Name of the sequence

Examples

sql

SELECT nextval('my_seq'); SELECT currval('my_seq');

→Both return the same value

sql

INSERT INTO orders (id) VALUES (nextval('order_seq')); SELECT currval('order_seq') AS new_order_id;

→Retrieve the just-inserted ID

sql

-- Safe pattern: call nextval once, use currval as many times as needed
SELECT nextval('invoice_seq');
INSERT INTO invoices (id, notes) VALUES (currval('invoice_seq'), 'draft');
INSERT INTO invoice_audit (invoice_id, event) VALUES (currval('invoice_seq'), 'created');

→currval returns the same value for both inserts — no extra sequence advance

sql

-- Demonstrate the session scope: currval is invisible to other connections
-- Session A:
SELECT nextval('my_seq'); -- returns 42
-- Session B (different connection):
SELECT currval('my_seq'); -- ERROR: currval of sequence not yet defined in this session

→currval is strictly per-session — other sessions cannot see it

⚠Anti-Pattern— Calling currval before nextval in the same session

Calling `currval('seq')` without having first called `nextval('seq')` in the same session raises a runtime error: `ERROR: currval of sequence "seq" is not yet defined in this session`. A common trigger for this bug is code that tries to retrieve the last inserted ID using `currval` in a fresh connection or after a connection pool recycles the connection, where the prior `nextval` was executed on a different session.

✓ Instead: Always call `nextval` in the same session before relying on `currval`. Better still, use `INSERT ... RETURNING id` which eliminates the dependency on `currval` entirely and is immune to session lifecycle issues.

For tables with `SERIAL` or `GENERATED ... AS IDENTITY`, prefer `INSERT ... RETURNING id` to get the new ID — it's cleaner and works correctly even with triggers that modify the row.

example

INSERT INTO orders (amount) VALUES (100) RETURNING id;

→Returns the new ID without needing currval

lastval

PG 7.4+→ bigint

Returns the most recent value returned by nextval for any sequence in the current session. Does not require knowing the sequence name.

DeveloperDBA

Signature

lastval ( ) → bigint

Examples

sql

SELECT nextval('my_seq'); SELECT lastval();

→Returns same value as the nextval call

sql

INSERT INTO t (name) VALUES ('Alice'); SELECT lastval() AS new_id;

→ID of the just-inserted row (if using SERIAL)

sql

-- Scenario where lastval returns an unexpected value due to a trigger
INSERT INTO orders (customer_id, total) VALUES (7, 250.00);
-- A trigger on orders also inserts into order_events, calling nextval('event_seq')
SELECT lastval(); -- Returns the event_seq value, NOT the orders id!

→lastval reflects the last nextval across ALL sequences — here it returns event_seq's value, not orders_id_seq

sql

-- lastval raises an error if no nextval has been called in the session
SELECT lastval(); -- fresh connection with no prior nextval

→ERROR: lastval is not yet defined in this session

⚠Anti-Pattern— Relying on sequence order for business logic

Because sequences can advance in any session independently, and because `nextval` calls are not rolled back, the numeric order of sequence values does not reflect insertion order, processing order, or any meaningful business timeline. Gaps can appear between consecutively inserted rows, and two concurrent transactions may commit in an order that is the reverse of their sequence values.

✓ Instead: Use a proper `created_at TIMESTAMPTZ DEFAULT now()` column to record insertion time. For ordering that must survive concurrent inserts, use `GENERATED ALWAYS AS IDENTITY` for the surrogate key and a separate timestamp or event-log table for the business ordering requirement.

`lastval()` returns the last `nextval` call across ALL sequences in the session — if any trigger or function calls `nextval` on another sequence, `lastval` will return that value instead. Prefer `RETURNING id` or `currval(specific_seq)` for safety.

example

-- Safe: 
INSERT INTO users (name) VALUES ($1) RETURNING id AS new_user_id;

→Unambiguous ID for the inserted row

setval

PG 7.4+→ bigint

Sets the sequence's current value. If the third argument is false, the next nextval call returns the specified value; otherwise (default), nextval returns value+1.

DeveloperDBA

Signatures

setval ( regclass, bigint ) → bigint

setval ( regclass, bigint, boolean ) → bigint

Parameters

Parameter	Type	Description
sequence_name	regclass	Sequence to modify
value	bigint	New current value for the sequence
is_called	boolean	If true (default), next nextval = value+increment. If false, next nextval = value.

Examples

sql

SELECT setval('my_seq', 1000);

→1000 (next nextval returns 1001)

sql

SELECT setval('my_seq', 1000, false);

→1000 (next nextval returns 1000)

sql

SELECT setval('orders_id_seq', max(id)) FROM orders;

→Resync sequence after bulk import

sql

-- Resync all sequences in public schema after a full data restore
DO $
DECLARE r RECORD;
BEGIN
  FOR r IN
    SELECT sequencename, schemaname FROM pg_sequences WHERE schemaname = 'public'
  LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE((SELECT MAX(id) FROM %I.%I), 1))',
      r.schemaname || '.' || r.sequencename,
      r.schemaname,
      replace(r.sequencename, '_id_seq', '')
    );
  END LOOP;
END;
$;

→All public sequences resynced (adapt table name derivation to your naming convention)

⚠Anti-Pattern— Confusing is_called semantics and setting the sequence off by one

The two-argument form `setval(seq, N)` is equivalent to `setval(seq, N, true)`, meaning `is_called = true` — the sequence treats N as already consumed, so the next `nextval` returns N+1 (or N+increment). A common mistake is wanting the next `nextval` to return exactly N (e.g., after a restore to make the sequence start at the highest existing ID + 1) but omitting `false`, resulting in the sequence skipping a value. Conversely, calling `setval(seq, N, false)` when you mean `is_called = true` causes the sequence to re-issue N, producing a duplicate key error on the next insert if N is already in the table.

✓ Instead: Be explicit every time: use `setval(seq, max_id, true)` when the next insert should get max_id + 1, or `setval(seq, next_id, false)` when the next insert should get exactly next_id. For a post-import resync, the safe idiom is `setval(pg_get_serial_sequence('t','id'), COALESCE((SELECT MAX(id) FROM t), 0) + 1, false)` — this sets the sequence so the next nextval returns MAX(id)+1 regardless of whether the table is empty.

After loading data with explicit IDs (pg_restore, data migration), the sequence is not automatically updated. Fix it with `SELECT setval('table_id_seq', (SELECT MAX(id) FROM table))`.

example

SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id), 0) + 1, false) FROM users;

→Sequence resynced to be safe for next INSERT

pg_get_serial_sequence

PG 7.4+→ text

Returns the name of the sequence associated with a SERIAL or GENERATED column, or NULL if none.

DeveloperDBA

Signature

pg_get_serial_sequence ( table_name text, column_name text ) → text

Parameters

Parameter	Type	Description
table_name	text	Table name (can include schema)
column_name	text	Column name

Examples

sql

SELECT pg_get_serial_sequence('users', 'id');

→public.users_id_seq

sql

SELECT setval(pg_get_serial_sequence('users', 'id'), max(id)) FROM users;

→Resync without hardcoding sequence name

sql

-- Check current sequence value alongside table max to detect drift
SELECT
  pg_get_serial_sequence('orders', 'id') AS seq_name,
  last_value AS seq_last,
  (SELECT MAX(id) FROM orders) AS table_max,
  last_value < (SELECT MAX(id) FROM orders) AS drift_detected
FROM pg_sequences
WHERE sequencename = 'orders_id_seq';

→drift_detected = true means the sequence is behind the table — next insert will fail with unique violation

sql

-- Returns NULL for columns not backed by a sequence
SELECT pg_get_serial_sequence('orders', 'total');

→NULL — 'total' is not a SERIAL or IDENTITY column

⚠Anti-Pattern— Using sequences for distributed unique IDs instead of UUIDs

A single PostgreSQL sequence is a bottleneck in any architecture involving multiple application servers writing to read replicas, sharded databases, logical replication targets, or external systems (e.g., a data warehouse). Sequences are local to one PostgreSQL cluster. If rows are ever merged from two clusters that each have their own sequence, integer IDs from both sides will collide. Additionally, sequential IDs leak row-count business intelligence (a competitor can estimate your growth by registering on your platform and observing ID values).

✓ Instead: For distributed or multi-tenant systems, use `gen_random_uuid()` (PG 13+, pgcrypto on earlier versions) to generate UUID v4 primary keys. They are globally unique without coordination, eliminate merge conflicts, and reveal nothing about table size. If you need a shorter surrogate, consider ULIDs or Snowflake-style IDs via a custom function, which are both sortable and distributed-safe.

Sequence names can vary between databases or after table renames. Use `pg_get_serial_sequence('table', 'column')` in scripts to look up the correct name dynamically.

example

SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders));

→Resync without hardcoding the sequence name

PostgreSQL Sequence Functions

Jump to function

What are PostgreSQL Sequence Functions?

nextval

Signature

Parameters

Examples

currval

Signature

Parameters

Examples

lastval

Signature

Examples

setval

Signatures

Parameters

Examples

pg_get_serial_sequence

Signature

Parameters

Examples

Related PostgreSQL Categories