Oracle and PostgreSQL sequences

With AWS DMS, you can manage database sequence objects across heterogeneous database platforms during migration. Sequences are unique identifiers that generate sequential numbers, often used as primary keys in tables.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		Sequences	Different syntax for a few options in PostgreSQL

Oracle usage

Sequences are database objects that serve as unique identity value generators, for example, automatically generating primary key values. Oracle treats sequences as independent objects. The same sequence can generate values for multiple tables.

Sequences can be configured with multiple parameters to control their value-generating behavior. For example, the INCREMENT BY sequence parameter defines the interval between each generated sequence value. If more than one database user is generating incremented values from the same sequence, each user may encounter gaps in the generated values that are visible to them.

Oracle 18c introduces scalable sequences: a special class of sequences that are optimized for multiple concurrent session usage.

This introduces the following new options when creating a new sequence:

• SCALE — enable the sequence scalability feature.

  • EXTEND — extend in additional 6 digits offset (as default) and the maximum number of digits in the sequence (maxvalue/minvalue).

  • NOEXTEND (default when using the SCALE option) — sequence value will be padded to the max value.

• NOSCALE — non-scalable sequence usage.

Oracle sequence options

By default, the initial and increment values for a sequence are both 1, with no upper limit.

• INCREMENT BY: Controls the sequence interval value of the increment or decrement (if a negative value is specified). If the INCREMENT BY parameter isn’t specified during sequence creation, the value is set to 1. The increment can’t be assigned a value of 0.

• START WITH: Defines the initial value of a sequence. The default value is 1.

• MAXVALUE | NOMAXVALUE: Specifies the maximum limit for values generated by a sequence. It must be equal or greater than the START WITH parameter and must be greater in value than the MINVALUE parameter. The default for NOMAXVALUE is 1027 for an ascending sequence.

• MINVALUE | NOMINVALUE: Specifies the minimum limit for values generated by a sequence. Must be less than or equal to the START WITH parameter and must be less than the MAXVALUE parameter. The default for NOMINVALUE is -1026 for a descending sequence.

• CYCLE | NOCYCLE: Instructs a sequence to continue generating values despite reaching the maximum value or the minimum value. If the sequence reaches one of the defined ascending limits, it generates a new value according to the minimum value. If it reaches a descending limit, it generates a new value according to the maximum value. NOCYCLE is the default.

• CACHE | NOCACHE: Specifies the number of sequence values to keep cached in memory for improved performance. CACHE has a minimum value of 2. The NOCACHE parameter causes a sequence to not cache values in memory. Specifying neither CACHE nor NOCACHE will cache 20 values to memory. In the event of a database failure, all unused cached sequence values are lost and gaps in sequence values may occur.

• SCALE | NOSCALE: Enable the scalable sequences feature (described above).

Examples

Create a sequence.

CREATE SEQUENCE SEQ_EMP
START WITH 100
INCREMENT BY 1
MAXVALUE 99999999999
CACHE 20
NOCYCLE;

Drop a sequence.

DROP SEQUENCE SEQ_EMP;

View sequences created for the current schema or user.

SELECT * FROM USER_SEQUENCES;

Use a sequence as part of an INSERT INTO statement.

CREATE TABLE EMP_SEQ_TST (COL1 NUMBER PRIMARY KEY, COL2 VARCHAR2(30));
INSERT INTO EMP_SEQ_TST VALUES(SEQ_EMP.NEXTVAL, 'A');

COL1    COL2
100     A

Query the current value of a sequence.

SELECT SEQ_EMP.CURRVAL FROM DUAL;

Manually increment the value of a sequence according to the INCREMENT BY specification.

SELECT SEQ_EMP.NEXTVAL FROM DUAL;

Alter an existing sequence.

ALTER SEQUENCE SEQ_EMP MAXVALUE 1000000;

Create a scalable sequence.

CREATE SEQUENCE scale_seq
MINVALUE 1
MAXVALUE 9999999999
SCALE;

select scale_seq.nextval as scale_seq from dual;

NEXTVAL
1010320001

Oracle 12c default values using sequences

Starting with Oracle 12c, you can assign a sequence to a table column with the CREATE TABLE statement and specify the NEXTVAL configuration of the sequence.

Generate DEFAULT values using sequences.

CREATE TABLE SEQ_TST ( COL1 NUMBER DEFAULT SEQ_1.NEXTVAL PRIMARY KEY, COL2 VARCHAR(30));
INSERT INTO SEQ_TST(COL2) VALUES('A');

SELECT * FROM SEQ_TST;

COL1   COL2
100    A

Oracle 12c session sequences (SESSION or GLOBAL)

Beginning with Oracle 12c, sequences can be created as session-level or global-level. By adding the SESSION parameter to a CREATE SEQUENCE statement, the sequence is created as a session-level sequence. Optionally, you can use the GLOBAL keyword to create a global sequence to provide consistent results across sessions in the database. Global sequences are the default. Session sequences return a unique range of sequence numbers only within a session.

Create Oracle 12c SESSION and GLOBAL sequences.

CREATE SEQUENCE SESSION_SEQ SESSION;
CREATE SEQUENCE SESSION_SEQ GLOBAL;

Oracle 12c identity columns

You can use sequences as an IDENTITY type, which automatically creates a sequence and associates it with the table column. The main difference is that there is no need to create a sequence manually; the IDENTITY type does that for you. An IDENTITY type is a sequence that can be configured.

Insert records using an Oracle 12c IDENTITY column (explicitly or implicitly).

INSERT INTO IDENTITY_TST(COL2) VALUES('A');
INSERT INTO IDENTITY_TST(COL1, COL2) VALUES(DEFAULT, 'B');
INSERT INTO IDENTITY_TST(col1, col2) VALUES(NULL, 'C');

SELECT * FROM IDENTITY_TST;

COL1    COL2
120     A
130     B

For more information, see CREATE SEQUENCE in the Oracle documentation.

PostgreSQL usage

Sequences in PostgreSQL serve the same purpose as in Oracle; they generate numeric identifiers automatically. The PostgreSQL CREATE SEQUENCE command is mostly compatible with the Oracle CREATE SEQUENCE command. A sequence object is owned by the user that created it.

Oracle 18c introduces scalable sequences, this feature isn’t always needed but if it and the current PostgreSQL isn’t scalable enough, you can use other solutions and services to allow high-concurrency data read (to store only sequences data), this option will require more changes in the application layer.

PostgreSQL sequence synopsis

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
[ AS data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]

Most PostgreSQL CREATE SEQUENCE parameters are compatible with Oracle. Similar to Oracle 12c, in PostgreSQL you can create a sequence and use it directly as part of a CREATE TABLE statement.

Sequence parameters

• TEMPORARY or TEMP — PostgreSQL can create a temporary sequence within a session. Once the session ends, the sequence is automatically dropped.

• IF NOT EXISTS — Creates a sequence even if a sequence with an identical name already exists. Replaces the existing sequence.

• AS — A new option in PostgreSQL 10. It is for specifying the data type of the sequence. The available options are smallint, integer, and bigint (default). This also determines the maximum and minimum values.

• INCREMENT BY — An optional parameter with a default value of 1. Positive values generate sequence values in ascending order. Negative values generate sequence values in descending sequence.

• START WITH — The same as Oracle. This is an optional parameter having a default of 1. It uses the MINVALUE for ascending sequences and the MAXVALUE for descending sequences.

• MAXVALUE | NO MAXVALUE — Defaults are between 263 for ascending sequences and -1 for descending sequences.

• MINVALUE | NO MINVALUE — Defaults are between 1 for ascending sequences and -263 for descending sequences.

• CYCLE | NO CYCLE — If the sequence value reaches MAXVALUE or MINVALUE, the CYCLE parameter instructs the sequence to return to the initial value (MINVALUE or MAXVALUE). The default is NO CYCLE.

• CACHE — Note that in PostgreSQL, the NOCACHE isn’t supported. By default, when not specifying the CACHE parameter, no sequence values will be pre-cached into memory, which is equivalent to the Oracle NOCACHE parameter. The minimum value is 1.

• OWNED BY | OWNBY NON — Specifies that the sequence object is to be associated with a specific column in a table, which isn’t supported by Oracle. When dropping this type of sequence, an error will be returned because of the sequence/table association.

Examples

Create a sequence.

CREATE SEQUENCE SEQ_1 START WITH 100
INCREMENT BY 1 MAXVALUE 99999999999 CACHE 20 NO CYCLE;

Identical to Oracle syntax, except for the whitespace in the NO CYCLE parameter.

Drop a sequence.

DROP SEQUENCE SEQ_1;

View sequences created in the current schema and sequence specifications.

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES;
OR
\ds

Use a PostgreSQL sequence as part of a CREATE TABLE and an INSERT statement.

CREATE TABLE SEQ_TST (COL1 NUMERIC DEFAULT NEXTVAL('SEQ_1') PRIMARY KEY, COL2 VARCHAR(30));

INSERT INTO SEQ_TST (COL2) VALUES('A');

SELECT * FROM SEQ_TST;
col1   col2
100    A

Use the OWNED BY parameter to associate the sequence with a table.

CREATE SEQUENCE SEQ_1 START WITH 100 INCREMENT BY 1 OWNED BY SEQ_TST.COL1;

Query the current value of a sequence.

SELECT CURRVAL('SEQ_1);

Manually increment a sequence value according to the INCREMENT BY value.

SELECT NEXTVAL('SEQ_1');
OR
SELECT SETVAL('SEQ_1', 200);

Alter an existing sequence.

ALTER SEQUENCE SEQ_1 MAXVALUE 1000000;

Note

To use the NEXTVAL function, the USAGE and UPDATE permissions on the sequence are needed. To use CURRVAL and LASTVAL functions, the USAGE and SELECT permissions on the sequence are needed.

Generating Sequence by SERIAL Type

PostgreSQL enables you to create a sequence that is similar to the AUTO_INCREMENT property supported by identity columns in Oracle 12c. When creating a new table, the sequence is created through the SERIAL data type. Other types from the same family are SMALLSERIAL and BIGSERIAL.

By assigning a SERIAL type to a column on table creation, PostgreSQL creates a sequence using the default configuration and adds a NOT NULL constraint to the column. The newly created sequence behaves like a regular sequence.

Examples

Using a SERIAL Sequence.

CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10));

INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A');
SELECT * FROM SERIAL_SEQ_TST;

col1   col2
1      A

\ds

Schema  Name                     Type      Owner
public  serial_seq_tst_col1_seq  sequence  pg_tst_db

Summary

Parameter or feature	Compatibility with PostgreSQL	Comments
Create sequence syntax	Full, with minor differences	See Exceptions
INCREMENT BY	Full
START WITH	Full
MAXVALUE and NOMAXVALUE	Full	Use NO MAXVALUE
MINVALUE and NOMINVALUE	Full	Use NO MINVALUE
CYCLE and NOCYCLE	Full	Use NO CYCLE
CACHE and NOCACHE	PostgreSQL doesn’t support the NOCACHE parameter but the default behavior is identical. The CACHE parameter is compatible with Oracle.
Default values using sequences in Oracle 12c	Supported by PostgreSQL	CREATE TABLE TBL( COL1 NUMERIC DEFAULT NEXTVAL ('SEQ_1')…
Session sequences (session or global) in Oracle 12c	Supported by PostgreSQL by using the TEMPORARY sequence parameter to Oracle SESSION sequence
Oracle 12c identity columns	Supported by PostgreSQL by using the SERIAL data type as sequence

For more information, see CREATE SEQUENCE, Sequence Manipulation Functions, and Numeric Types in the PostgreSQL documentation.