Sequences

A sequence is a named counter that returns one integer at a time.

Use a sequence when the number itself has meaning to people or external systems: an invoice number, an order number, a ticket number, or a short reference printed on a receipt.

Do not use a sequence just because a table needs a primary key. For primary keys, prefer an OID from gen_id(), or a UUID from gen_uuid_v4() or gen_uuid_v7():

CREATE TABLE orders (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  order_no INT64 GENERATED BY DEFAULT AS IDENTITY,
  total FLOAT64 NOT NULL
);

The id is the database key, which each writer generates without coordinating with other writers. The order_no is the human-facing number.

gen_id() and gen_uuid_v7() produce time-ordered values, so with key-range sharding every insert goes to the last range (a contiguous slice of keys) of the primary index. In that case, use gen_uuid_v4() for tables with a high insert rate. See Best practices.

That distinction matters in a distributed database. A sequence is one logical counter. Inserts that need a value from it coordinate through that counter, so a sequence can become the table's point of contention. CACHE can reduce how often the counter commits, but it does not make a sequence as independent as an OID or UUID generator.

Quick start

Create a sequence, then use nextval as a column default:

CREATE SEQUENCE order_no START WITH 1000;

CREATE TABLE orders (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  no INT64 NOT NULL DEFAULT (nextval('order_no')),
  total FLOAT64 NOT NULL
);

INSERT INTO orders (total) VALUES (19.90);
SELECT no, total FROM orders;

If only one table column uses the sequence, an identity column is usually the cleaner way to write it:

CREATE TABLE invoices (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  invoice_no INT64 GENERATED BY DEFAULT AS IDENTITY,
  total FLOAT64 NOT NULL
);

CamusDB creates and owns the backing sequence for the identity column.

Create a sequence

CREATE SEQUENCE [IF NOT EXISTS] name [ option ... ];

Common options:

Option	Default	Meaning
START [WITH] n	MINVALUE	First value the sequence returns.
INCREMENT [BY] n	1	Step between values. Must be positive.
MINVALUE n / NO MINVALUE	1	Lowest value the sequence may hold.
MAXVALUE n / NO MAXVALUE	none	Highest value the sequence may return.
CACHE n	1	Values reserved per durable counter update.
NO CYCLE	accepted	The only supported cycle behavior.

Example:

CREATE SEQUENCE invoice_no
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  MAXVALUE 999999
  CACHE 1;

Sequences share a namespace with tables and views, so a table and a sequence cannot have the same name in one database.

CYCLE is not supported, because wrapping back to the minimum would reissue values. A sequence issues each value once, unless setval, ALTER SEQUENCE ... RESTART, or TRUNCATE ... RESTART IDENTITY moves the counter back.

Use values

Sequence functions:

Function	Meaning
nextval('s')	Advance s and return the new value.
currval('s')	Return the last value this transaction drew from s.
lastval()	Return the last value this transaction drew from any sequence.
setval('s', n [, is_called])	Move the counter.

nextval

Use nextval in a column default or in an insert value:

INSERT INTO orders (id, no, total)
VALUES (gen_id(), nextval('order_no'), 25.00);

CamusDB reserves the values a statement needs before the statement runs, so sequence calls are accepted only where the engine can know the count in advance:

• INSERT ... VALUES expressions.
• Column defaults used by INSERT ... VALUES.
• Column defaults used by INSERT ... SELECT.
• A SELECT with no FROM clause.

Other positions are rejected with CADB0547, including filters, joins, aggregates, stored view bodies, check constraints, and UPDATE ... SET.

currval and lastval

currval and lastval are scoped to the transaction:

BEGIN;
INSERT INTO orders (total) VALUES (19.90);
SELECT currval('order_no');
COMMIT;

Outside an explicit transaction, each statement is its own transaction, so a later statement cannot ask what a previous autocommit statement drew; it gets CADB0546.

setval

Use setval to move a free-standing sequence:

SELECT setval('order_no', 5000);          -- next nextval returns 5001
SELECT setval('order_no', 5000, false);   -- next nextval returns 5000

setval is accepted only as the whole projection of a SELECT without FROM, with at most one setval call in the statement.

Moving a counter down can make the sequence reissue values that committed rows already hold. If the sequence feeds a unique column, later inserts can then fail with a duplicate-key error. Check the table first, or move the sequence above the highest stored value.

Identity columns

Identity columns are table columns backed by owned sequences:

CREATE TABLE tickets (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  ticket_no INT64 GENERATED ALWAYS AS IDENTITY,
  subject STRING NOT NULL
);

CREATE TABLE notes (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  note_no INT64 GENERATED BY DEFAULT AS IDENTITY,
  body STRING NOT NULL
);

With GENERATED ALWAYS AS IDENTITY, callers cannot supply their own value for the column. Name the insert columns and leave out the identity column:

INSERT INTO tickets (subject) VALUES ('Boarding pass printer');

With GENERATED BY DEFAULT AS IDENTITY, the sequence supplies a value only when the insert omits the column. If an insert supplies a value, CamusDB writes it and does not advance the sequence.

CamusDB also accepts SERIAL and BIGSERIAL as shorthand for an INT64 column backed by an owned sequence. Both are 64-bit, unlike PostgreSQL SERIAL. A sequence on a SMALLINT or INT column keeps its own bounds; a generated value outside the column range fails the write with CADB0420.

CREATE TABLE invoices (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  invoice_no SERIAL,
  total FLOAT64 NOT NULL
);

Owned sequences

An identity sequence belongs to its column.

• DROP SEQUENCE on the owned sequence is rejected with CADB0548.
• Dropping the column or dropping the table with FORCE removes the sequence.
• TRUNCATE ... RESTART IDENTITY restarts the owned sequence.
• TRUNCATE ... CONTINUE IDENTITY leaves it alone.

A free-standing sequence used in a default is not owned by the table:

CREATE SEQUENCE shared_no;

CREATE TABLE a (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  no INT64 DEFAULT (nextval('shared_no'))
);

CREATE TABLE b (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  no INT64 DEFAULT (nextval('shared_no'))
);

Use this shape only when sharing one counter across tables is intentional.

Truncate and identity

TRUNCATE TABLE orders CONTINUE IDENTITY;  -- default
TRUNCATE TABLE orders RESTART IDENTITY;

RESTART IDENTITY restarts only sequences owned by columns of the truncated table. It does not reset a free-standing sequence that a default calls with nextval, because another table may be using that same counter.

Change or drop a sequence

ALTER SEQUENCE order_no RESTART WITH 500;
ALTER SEQUENCE order_no RESTART;
ALTER SEQUENCE order_no INCREMENT BY 10;
ALTER SEQUENCE order_no MAXVALUE 100000;
ALTER SEQUENCE order_no NO MAXVALUE;
ALTER SEQUENCE order_no RENAME TO order_number;
DROP SEQUENCE order_number;

CamusDB checks an ALTER against the definition it produces. For example, if you raise MINVALUE above the recorded START, change both together:

ALTER SEQUENCE order_no MINVALUE 100 START WITH 100;

ALTER SEQUENCE ... RESTART cannot run inside an explicit transaction, because moving a counter is immediate and a later ROLLBACK cannot undo it.

Inspect sequences

SHOW SEQUENCES;
SHOW SEQUENCES LIKE 'order%';
SHOW CREATE SEQUENCE order_no;
COMMENT ON SEQUENCE order_no IS 'one per customer order';

SHOW SEQUENCES reports the name, reserved position, start value, increment, minimum, maximum, cache, owning column, and comment.

The reserved_upto column is a ceiling, not necessarily the last value issued:

CREATE SEQUENCE s CACHE 1000;
SHOW SEQUENCES LIKE 's';        -- reserved_upto is NULL

SELECT nextval('s');            -- 1
SELECT nextval('s');            -- 2
SHOW SEQUENCES LIKE 's';        -- reserved_upto is 1000

With CACHE 1, the default, reserved_upto advances one value at a time. With a larger cache, it shows the top of the reserved block.

Distributed behavior

Sequences are durable and replicated (copied to other nodes), but they are still counters. Keep these properties in mind:

• A sequence issues unique values, unless setval or a RESTART moves the counter back.
• Sequence values are not a perfect insert order. During leadership changes (when another node becomes the leader that issues values), a later insert can receive a lower value than an earlier insert.
• Values are not rolled back. A failed or rolled-back transaction can leave a gap.
• CACHE improves throughput by reserving a block of values at a time. Unused values from a block can be skipped after restart, eviction, or leadership movement.
• setval, ALTER SEQUENCE ... RESTART, and TRUNCATE ... RESTART IDENTITY can wait for the storage lease, a time-limited claim of about five seconds, so stale cached blocks cannot keep issuing values.

These are normal sequence semantics, similar to PostgreSQL's, and they are why sequences suit external numbers but are not ideal as the physical key of high-write tables.

COALESCE, IFNULL, and NVL stop evaluating at their first non-NULL value. A NEXTVAL after a non-NULL literal or parameter proven to be returned draws nothing: COALESCE(1, nextval('s')) does not advance s. If the statement cannot prove a call unreachable, it can reserve a value that is never used, leaving a gap. A sequence call in a nested FROM-less SELECT is refused with CADB0547. See conditional evaluation.

Branches, backups, and drops

Dropping a database removes its sequence counters.

Branching a database gives the branch its own counters, seeded above the source's reserved ceiling. This avoids collisions with rows inherited from the source, at the cost of a possible gap. See Database Branching.

Backups carry sequence records. Restoring a backup restores the counters along with the rows, at the reserved ceiling, so you do not need to reseed them by hand. See Backup And Restore.

Privileges

Sequence statements reuse existing database privileges:

Statement	Privilege on the database
CREATE SEQUENCE	CreateTable
DROP SEQUENCE	Drop
ALTER SEQUENCE, COMMENT ON SEQUENCE	Alter
SHOW SEQUENCES, SHOW CREATE SEQUENCE	Select
nextval, setval	Update
currval, lastval	Select

Errors

Code	Meaning
CADB0542	The name is already taken by a sequence, table, or view.
CADB0543	The named sequence does not exist.
CADB0544	The sequence has no value left to issue.
CADB0545	The sequence definition is invalid.
CADB0546	currval or lastval was called before this transaction drew a value.
CADB0547	A sequence call was written where CamusDB cannot know how many values it would draw.
CADB0548	The sequence is owned by an identity column, or another object depends on it.
CADB0535	The partition that stores the sequence had no confirmed leader for the retry window. Retry the statement.

See also

• Tables And Columns
• Data Types
• TRUNCATE TABLE
• Database Branching
• Configuration, for sequence_retry_budget_ms.