# 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 | | --- | --- | --- | --- | | ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png) | ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png) | [Sequences](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.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` \$1 `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` \$1 `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` \$1 `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` \$1 `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` \$1 `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](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SEQUENCE.html#GUID-E9C78A8C-615A-4757-B2A8-5E6EFB130571) 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` \$1 `NO MAXVALUE` — Defaults are between 263 for ascending sequences and -1 for descending sequences. + `MINVALUE` \$1 `NO MINVALUE` — Defaults are between 1 for ascending sequences and -263 for descending sequences. + `CYCLE` \$1 `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` \$1 `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](https://www.postgresql.org/docs/13/static/sql-createsequence.html), [Sequence Manipulation Functions](https://www.postgresql.org/docs/13/static/functions-sequence.html), and [Numeric Types](https://www.postgresql.org/docs/13/static/datatype-numeric.html) in the *PostgreSQL documentation*.