Oracle-Compatible Sequence Syntax & Behavior in PolarDB - PolarDB

Defines a new sequence generator for producing unique integers automatically—commonly used to generate primary key values.

Syntax

CREATE SEQUENCE name [ INCREMENT BY increment ]
  [ { NOMINVALUE | MINVALUE minvalue } ]
  [ { NOMAXVALUE | MAXVALUE maxvalue } ]
  [ START WITH start ] [ CACHE cache | NOCACHE ] [ CYCLE ]

Description

CREATE SEQUENCE creates a sequence generator and initializes a single-row internal table named name. The user who runs the command owns the generator.

If you specify a schema, the sequence is created in that schema. Otherwise, it is created in the current schema. The sequence name must not conflict with any other sequence, table, index, or view in the same schema.

After creating a sequence, use NEXTVAL and CURRVAL to generate and read values.

Parameters

Parameter	Description
`name`	The name of the sequence. Can be schema-qualified.
`INCREMENT BY increment`	The value added to the current sequence value on each call to `NEXTVAL`. A positive value creates an ascending sequence; a negative value creates a descending sequence. Default: `1`.
`MINVALUE minvalue` / `NOMINVALUE`	The minimum value the sequence can generate. Default for ascending sequences: `1`. Default for descending sequences: -2<sup>63</sup> - 1. Use `NOMINVALUE` to restore the default.
`MAXVALUE maxvalue` / `NOMAXVALUE`	The maximum value the sequence can generate. Default for ascending sequences: 2<sup>63</sup> - 1. Default for descending sequences: `-1`. Use `NOMAXVALUE` to restore the default.
`START WITH start`	The first value returned by the sequence. Ascending sequences default to `minvalue`; descending sequences default to `maxvalue`.
`CACHE cache` / `NOCACHE`	The number of sequence values to preallocate in memory per session for faster access. Minimum: `1` (equivalent to `NOCACHE`). Default: `1`.
`CYCLE`	Allows the sequence to wrap around after reaching its limit. An ascending sequence restarts from `minvalue`; a descending sequence restarts from `maxvalue`. Without `CYCLE`, any `NEXTVAL` call past the limit returns an error. Use `NO CYCLE` to restore the default — `NO CYCLE` is not Oracle-compatible.

Usage notes

Integer range. Sequences use big integer arithmetic. The supported range is -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. On older platforms without eight-byte integer support, the range is -2,147,483,648 to +2,147,483,647.

CACHE and concurrent sessions. When cache is greater than 1, each session preallocates a block of values on its first access. Those values are reserved for that session and not visible to others. When the session ends, any unused values in its block are lost, leaving gaps in the overall sequence. For example, with cache = 10, Session A might hold values 1–10 (returning NEXTVAL = 1), while Session B holds 11–20 (returning NEXTVAL = 11) before Session A reaches 2. Set cache = 1 (or NOCACHE) if strictly sequential values are required.

Examples

Create an ascending sequence named serial that starts at 101:

CREATE SEQUENCE serial START WITH 101;

Retrieve the next value:

SELECT serial.NEXTVAL FROM DUAL;

Output:

 nextval
---------
     101
(1 row)

Create a sequence named supplier_seq with no caching:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Retrieve the next value:

SELECT supplier_seq.NEXTVAL FROM DUAL;

Output:

 nextval
---------
       1
(1 row)