Oracle Sequences Don't Map 1:1 to Postgres — Here's the Gap

The Surface Similarity That Hides the Real Gap

On the surface, Oracle and Postgres sequences look nearly identical. Both generate monotonically increasing integers. Both support INCREMENT BY, MINVALUE, MAXVALUE, START WITH, CACHE, CYCLE, and ORDER options. Both expose a function to advance the sequence and return the new value. Migration tools convert Oracle sequences to Postgres sequences automatically, the DDL compares cleanly, and QA signs off because unit tests pass.

The gaps appear in production, weeks after go-live, in specific code paths that exercise behaviors the tests didn't cover. Here is the complete set of behavioral differences that cause post-migration bugs.

Gap 1: CURRVAL Scope

In Oracle, sequence_name.CURRVAL returns the most recently generated value from that sequence within the current session. The key word is "current session." Each session has its own CURRVAL state, and CURRVAL can only be called after NEXTVAL has been called at least once in that session.

In Postgres, currval('sequence_name') behaves identically — it returns the last value generated by nextval() in the current session. This is one area where the semantics genuinely match.

The gap appears in connection-pooled applications. When an application calls NEXTVAL via PgBouncer in transaction mode, the backend that executes the NEXTVAL call may be different from the backend that executes the subsequent CURRVAL call. CURRVAL is session-scoped on the backend, not the application logical session. The application calls NEXTVAL, gets value 5001, the transaction commits, the backend is returned to the pool, and the next call — CURRVAL — lands on a different backend where the last NEXTVAL was something completely different.

-- Oracle application pattern that breaks behind transaction-mode PgBouncer
-- Step 1: get next ID
INSERT INTO orders (id, customer_id, status)
VALUES (order_seq.NEXTVAL, :cust_id, 'pending');

-- Step 2: retrieve the ID just inserted (separate statement)
SELECT order_seq.CURRVAL FROM dual;  -- Oracle
-- Postgres equivalent
SELECT currval('order_seq');  -- WRONG if connection pool reassigned backend

-- The safe pattern in both Oracle and Postgres:
-- Return the value at INSERT time, not via a separate CURRVAL call
INSERT INTO orders (id, customer_id, status)
VALUES (nextval('order_seq'), 42, 'pending')
RETURNING id;

The fix: eliminate any application pattern that calls CURRVAL in a separate statement from the NEXTVAL call. Use RETURNING id in the INSERT statement to capture the generated value at the moment of insertion. This is safer in both Oracle (using the RETURNING INTO clause) and Postgres.

Gap 2: The NOCACHE Restart Behavior

Oracle sequences with CACHE 0 (NOCACHE) persist the current sequence value to the data dictionary on every NEXTVAL call. This means no gaps when the database restarts — the sequence resumes from exactly where it left off.

Postgres sequences do not have a NOCACHE equivalent that eliminates restart gaps. Even with CACHE 1 (the minimum), Postgres does not guarantee gap-free sequences across instance restarts. When the Postgres instance restarts, the sequence value is reset based on the last committed value — but in-memory state that was never committed may be lost, creating gaps.

More significantly: Oracle sequences with a large CACHE value (say, CACHE 100) will lose the uncached values on instance restart, creating a gap of up to 100 in the sequence. Oracle documents this clearly. What Oracle DBAs sometimes miss is that this is also true in Postgres — and the gap behavior on restart differs subtly between Oracle and Postgres even for equivalent CACHE settings.

-- Oracle NOCACHE sequence
CREATE SEQUENCE order_seq
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOORDER
  NOCYCLE;

-- Postgres approximation (closest, not identical)
CREATE SEQUENCE order_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 1
  NO CYCLE;

-- Check current sequence state in Postgres
SELECT last_value, is_called FROM order_seq;

-- Check all sequences and their cache settings
SELECT schemaname, sequencename, last_value, increment_by, cycle_option, cache_size
FROM pg_sequences
WHERE schemaname = 'public'
ORDER BY sequencename;

If your application has a business requirement for gap-free sequences (common in invoice numbering, check numbers, or regulatory reporting), Oracle's NOCACHE does not map to any Postgres sequence option. Gap-free numbering in Postgres requires a different mechanism entirely: a dedicated serial table with SELECT FOR UPDATE, or an application-level lock around the sequence fetch.

-- Gap-free sequence pattern in Postgres (for compliance requirements)
-- Uses a table with explicit locking instead of a sequence
CREATE TABLE gapfree_counters (
  name TEXT PRIMARY KEY,
  current_value BIGINT NOT NULL DEFAULT 0
);

INSERT INTO gapfree_counters (name, current_value) VALUES ('invoice_seq', 0);

-- Atomic increment function
CREATE OR REPLACE FUNCTION next_gapfree_value(seq_name TEXT)
RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE
  next_val BIGINT;
BEGIN
  UPDATE gapfree_counters
  SET current_value = current_value + 1
  WHERE name = seq_name
  RETURNING current_value INTO next_val;
  RETURN next_val;
END;
$$;

Gap 3: The ORDER Option in RAC vs Postgres

Oracle RAC deployments can run sequences with the ORDER option, which guarantees that sequence values are generated in request order across all RAC nodes. Without ORDER, each RAC node caches sequence values independently, and the values from different nodes may interleave in non-chronological order (node 1 might generate 1, 3, 5 while node 2 generates 2, 4, 6 from its cache).

Postgres has no equivalent to Oracle's ORDER option because Postgres doesn't have a RAC-equivalent shared-disk multi-master architecture. In Postgres, there is one primary that owns the sequence. All sequence increments go through the primary. This actually means Postgres sequences are inherently ordered in generation time — you get Oracle ORDER behavior by default.

The gap is in the assumption: Oracle code written for a single-instance Oracle database (not RAC) may have relied on sequence values being roughly chronological as a proxy for insert order. In Postgres with connection pooling and concurrent inserts, sequence values are still monotonically increasing but the order in which applications receive them may differ from insertion order if multiple transactions are in flight simultaneously. This is true in Oracle too, but teams that migrate from single-instance Oracle sometimes encounter this for the first time in a Postgres environment with higher concurrency.

Gap 4: CYCLE Wraparound Semantics

Oracle and Postgres both support CYCLE sequences that wrap back to MINVALUE after hitting MAXVALUE. The semantics match on the surface but differ in one edge case: what happens when a cycled sequence wraps and the newly generated value conflicts with an existing primary key.

In Oracle, the sequence wraps and returns the MINVALUE. Whether that causes a primary key constraint violation is the application's problem — the sequence doesn't know about the table it's feeding. In Postgres, the behavior is identical. Both databases will happily return a value that, if inserted, would violate a unique constraint.

The migration risk: Oracle applications that use CYCLE sequences often have been running for years without wrapping. During migration, the sequence current value is migrated correctly. But the MAXVALUE may be much lower than the application ever approaches, and the CYCLE option was added "just in case" years ago. After migration, if the team adjusts the sequence settings or the START WITH value incorrectly, they can create a sequence that wraps immediately — an issue that would never have appeared in the Oracle environment.

-- Check for sequences approaching their MAXVALUE in Postgres
SELECT
  schemaname,
  sequencename,
  last_value,
  max_value,
  ROUND(100.0 * last_value / NULLIF(max_value, 0), 2) AS pct_exhausted,
  cycle_option
FROM pg_sequences
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pct_exhausted DESC NULLS LAST;

Gap 5: NEXTVAL in DEFAULT Clauses

Oracle allows sequences in DEFAULT column expressions in 12c and later:

-- Oracle 12c+ DEFAULT sequence in column definition
CREATE TABLE orders (
  id        NUMBER DEFAULT order_seq.NEXTVAL PRIMARY KEY,
  status    VARCHAR2(20)
);

Postgres handles this via identity columns (PostgreSQL 10+) or the older serial pseudo-type. The migration path is straightforward but the syntax differs:

-- Postgres: identity column (recommended, SQL standard)
CREATE TABLE orders (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT
);

-- Postgres: attach existing sequence as default (when migrating existing seq)
CREATE SEQUENCE order_seq START WITH 1;
CREATE TABLE orders (
  id     BIGINT DEFAULT nextval('order_seq') PRIMARY KEY,
  status TEXT
);
ALTER SEQUENCE order_seq OWNED BY orders.id;

-- Postgres: legacy serial type (avoid for new tables)
CREATE TABLE orders (
  id     BIGSERIAL PRIMARY KEY,
  status TEXT
);

The GENERATED ALWAYS AS IDENTITY syntax is preferred for new Postgres tables. The OWNED BY clause on the sequence ensures that dropping the table also drops the sequence — matching Oracle's behavior when a sequence is the sole feeder of a table's primary key column.

Gap 6: Cross-Schema Sequence References

Oracle applications sometimes reference sequences from a different schema than the table they're feeding: schema_b.order_seq.NEXTVAL inserted into schema_a.orders. Permissions allowing, this works transparently.

Postgres allows the same pattern — nextval('schema_b.order_seq') — but the schema search_path behavior introduces a subtlety. If the application code uses an unqualified sequence name and relies on the search_path to resolve it, a different search_path in Postgres may resolve the same name to a different sequence. Always use fully qualified sequence names after migration.

The Migration Checklist

Before signing off on an Oracle-to-Postgres sequence migration:

• Audit all CURRVAL usage. Ensure it's in the same statement as the NEXTVAL call or replaced with RETURNING.
• Identify any sequences used for gap-free numbering. These need application-level locking in Postgres, not a sequence.
• Check all CYCLE sequences for proximity to MAXVALUE. Add monitoring against pg_sequences for sequences above 80% exhausted.
• Replace NOCACHE sequences with CACHE 1 and document that restart gaps are possible if the application previously depended on gap-free behavior.
• Verify connection pool mode. Any application that uses CURRVAL in a separate statement from NEXTVAL must either switch to RETURNING or be pinned to session mode in PgBouncer.
• Test sequence ownership with OWNED BY so table drops cascade correctly.

Sequences are one of those Oracle-to-Postgres migration items that look trivially simple on paper and create subtle, hard-to-diagnose production bugs when the differences aren't accounted for explicitly.