Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle and PostgreSQL sequences

With Amazon 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.

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:

Oracle sequence options

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

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

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;

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

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