Oracle Sequences vs PostgreSQL: What Changes and Why

Sequences Look Identical — Until They Aren't

Sequences are one of the most portable constructs in any Oracle → PostgreSQL migration. Both databases have CREATE SEQUENCE, both expose NEXTVAL and CURRVAL, both support START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CACHE, and CYCLE. The mental model maps directly.

But three things differ in practice — the syntax for calling a sequence in a DEFAULT clause, the modern replacement for the old "sequence + BEFORE INSERT trigger" pattern, and the post-migration sequence-resync step. The first two are mechanical; the third is the single most common source of "duplicate key value violates unique constraint" errors after cutover.

This guide walks through each — with concrete before / after SQL.

What's the Same

The basic CREATE SEQUENCE syntax is identical on both sides. Every common option works:

-- Identical on Oracle and PostgreSQL
CREATE SEQUENCE employees_seq
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 999999999
  CACHE 20
  NOCYCLE;

-- Get the next value
SELECT employees_seq.NEXTVAL FROM dual;   -- Oracle
SELECT nextval('employees_seq');          -- PostgreSQL

-- Get the current value (in the same session)
SELECT employees_seq.CURRVAL FROM dual;   -- Oracle
SELECT currval('employees_seq');          -- PostgreSQL

The function-call form is the only structural difference — PostgreSQL doesn't use the dot-notation seq.NEXTVAL; it uses standard SQL function syntax with a quoted name. Every modern transpiler converts this automatically.

Calling a Sequence in a DEFAULT Clause

The default-clause case is where the syntax difference matters in DDL. Old-school Oracle patterns embed the sequence call right in the column default:

-- Oracle (pre-12c pattern)
CREATE TABLE orders (
  order_id   NUMBER(10)   DEFAULT employees_seq.NEXTVAL PRIMARY KEY,
  customer   VARCHAR2(60),
  created_at DATE         DEFAULT SYSDATE
);

-- PostgreSQL — mechanical rewrite of the sequence call
CREATE TABLE orders (
  order_id   INTEGER      DEFAULT nextval('employees_seq') PRIMARY KEY,
  customer   VARCHAR(60),
  created_at TIMESTAMP    DEFAULT NOW()
);

Note the two-character pattern: X.NEXTVAL becomes nextval('X'). The sequence name in PostgreSQL is quoted as a string. The transpiler handles this without human input.

IDENTITY Columns — The Modern Replacement

Since Oracle 12c and PostgreSQL 10, both databases support the SQL-standard GENERATED AS IDENTITYsyntax. This is the recommended pattern for new tables on both sides — it ties the sequence to the column, removes the need to name the sequence explicitly, and behaves cleanly with bulk loads.

-- Identical on both sides:
CREATE TABLE orders (
  order_id   INTEGER      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer   VARCHAR(60),
  created_at TIMESTAMP    DEFAULT NOW()
);

-- Both also support GENERATED BY DEFAULT AS IDENTITY,
-- which allows explicit overrides during bulk INSERT (useful for migrations):
CREATE TABLE orders_migrated (
  order_id   INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer   VARCHAR(60)
);

Migration tip: use GENERATED BY DEFAULT AS IDENTITY for migrated tables, not ALWAYS. ALWAYSrejects explicit values during INSERT, which means your bulk-load step has to strip the identity column from the INSERT list. BY DEFAULT lets the bulk load preserve original IDs, then the sequence picks up from where it left off for new rows. Every modern migration tool defaults to this.

The Legacy Sequence + Trigger Pattern

Many older Oracle schemas predate identity columns. The pattern looks like:

-- Pre-12c Oracle pattern: sequence + BEFORE INSERT trigger
CREATE SEQUENCE orders_id_seq START WITH 1 INCREMENT BY 1 CACHE 100;

CREATE OR REPLACE TRIGGER trg_orders_id
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.order_id IS NULL)
BEGIN
  :NEW.order_id := orders_id_seq.NEXTVAL;
END;
/

On migration, the modernization opportunity is to drop the trigger entirely and replace the whole thing with an IDENTITY column. The behaviour is equivalent, the surface area is smaller, and PostgreSQL's IDENTITY implementation is well-optimised:

-- PostgreSQL equivalent — no trigger needed
ALTER TABLE orders
  ALTER COLUMN order_id
    ADD GENERATED BY DEFAULT AS IDENTITY (START WITH <max+1> CACHE 100);

-- Drop the no-longer-needed sequence and trigger
-- (The migration tool flags these as redundant in the post-conversion report.)

Oracle-only Sequence Options

A few Oracle sequence options don't have PostgreSQL equivalents. Most are either RAC-specific or default behaviours that no longer need a keyword. The transpiler silently drops them:

The Post-Migration Sequence-Resync Trap

This is where most cutover bugs come from. After bulk-loading data into PostgreSQL, the sequences start at 1 by default. The next application INSERT collides with row IDs already loaded:

-- After bulk load: rows have IDs 1 through 50,000.
-- Sequence orders_id_seq is at... 1.
SELECT MAX(order_id) FROM orders;          -- → 50000
SELECT last_value FROM orders_id_seq;      -- → 1

-- Next INSERT fails:
INSERT INTO orders (customer) VALUES ('Alice');
-- ERROR: duplicate key value violates unique constraint "orders_pkey"

The fix is to advance every sequence past the current max value. A single SQL block walks all sequences in the schema and resyncs them:

-- Resync every sequence in the current schema to MAX(id) + 1.
-- Works for both legacy sequence+trigger patterns AND IDENTITY columns
-- (PG stores the IDENTITY's sequence in pg_sequences).
DO $$
DECLARE
  rec RECORD;
  v_max BIGINT;
  v_table TEXT;
  v_column TEXT;
BEGIN
  FOR rec IN
    SELECT s.schemaname, s.sequencename,
           pg_get_serial_sequence(c.table_schema || '.' || c.table_name, c.column_name) AS owned,
           c.table_schema, c.table_name, c.column_name
    FROM pg_sequences s
    LEFT JOIN information_schema.columns c
      ON pg_get_serial_sequence(c.table_schema || '.' || c.table_name, c.column_name)
         = s.schemaname || '.' || s.sequencename
    WHERE s.schemaname = current_schema()
  LOOP
    IF rec.owned IS NOT NULL THEN
      EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
                     rec.column_name, rec.table_schema, rec.table_name)
        INTO v_max;
      EXECUTE format('SELECT setval(%L, %s)', rec.owned, v_max + 1);
      RAISE NOTICE 'Resynced %.% to %', rec.table_name, rec.column_name, v_max + 1;
    END IF;
  END LOOP;
END $$;

Every migration tool worth using runs an equivalent step automatically as part of the post-load phase. If you're hand-rolling a migration, this is the single most important post-load step — forget it and your application breaks the first time it tries to insert.

CYCLE, MINVALUE, MAXVALUE

Both DBs support sequence cycling — when the sequence hits MAXVALUE, it wraps back to MINVALUE. The defaults differ slightly:

• Default MAXVALUE: Oracle = 10²⁸ − 1; PostgreSQL = 2⁶³ − 1 for BIGINT-backed sequences. Both are effectively unbounded for application use.
• Default MINVALUE: 1 on both sides for ascending sequences.
• CYCLE / NOCYCLE: identical keywords, identical behaviour.

The Honest Verdict

Sequences are one of the most portable constructs in any migration. The function-call syntax is the only structural change, and modern transpilers do that automatically. The legacy sequence + trigger pattern is a modernization opportunity — replace it with GENERATED BY DEFAULT AS IDENTITYand your schema gets simpler.

The one thing not to skip is the post-load resync step. It's a one-time fix, takes seconds, and prevents the most embarrassing class of cutover bug. Any tool that doesn't do it for you automatically — write your own resync block and run it as the last step before unfreezing the application.