Oracle identity columns and PostgreSQL SERIAL type

With AWS DMS, you can migrate databases that utilize identity columns or auto-incrementing primary keys across different database engines. Oracle databases use identity columns to automatically generate unique sequential values for primary keys, while PostgreSQL databases employ the SERIAL pseudo-type for the same purpose.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		Sequences	Since PostgreSQL 10, there are no differences besides the data types.

Oracle usage

Oracle 12c introduced support for automatic generation of values to populate columns in database tables. The IDENTITY type generates a sequence and associates it with a table column without the need to manually create a separate sequence object. The IDENTITY type relies (internally) on sequences, which can also be manually configured.

Examples

Create a table with an Oracle 12c identity column.

CREATE TABLE IDENTITY_TST (COL1 NUMBER GENERATED BY DEFAULT AS IDENTITY(START WITH 100
INCREMENT BY 10), COL2 VARCHAR2(30));

Insert data into the table. The identity column automatically generates values for COL1.

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
---    ---
100    A
110    B

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

PostgreSQL usage

PostgreSQL enables you to create a sequence that is similar to the IDENTITY property supported by Oracle 12c identity column feature. When creating a new table using the SERIAL pseudo-type, a sequence is created.

Additional types from the same family are SMALLSERIAL and BIGSERIAL.

By assigning a SERIAL type to a column as part of table creation, PostgreSQL creates a sequence using default configuration and adds the NOT NULL constraint to the column. The new sequence can be altered and configured as a regular sequence.

Since PostgreSQL 10, there is a new option called identity columns which is similar to SERIAL data type but more SQL standard compliant. The identity columns are highly compatibility compare to Oracle identity columns.

Examples

Using the PostgreSQL SERIAL pseudo-type (with a Sequence that is created implicitly).

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

ALTER SEQUENCE SERIAL_SEQ_TST_COL1_SEQ RESTART WITH 100 INCREMENT BY 10;
INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A');
INSERT INTO SERIAL_SEQ_TST(COL1, COL2) VALUES(DEFAULT, 'B');
SELECT * FROM SERIAL_SEQ_TST;

To create a table with identity columns, use the following (this command is Oracle compatible).

CREATE TABLE emps (
emp_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL);

INSERT INTO emps (emp_name) VALUES ('Robert');
INSERT INTO emps (emp_id, emp_name) VALUES (DEFAULT, 'Brian');

Note

It is important to know that in PostgreSQL (both SERIAL and IDENTITY), you can insert any value that you want that won’t violate the primary key constraint. If you do that and after that, you will use the identity column sequence value again, the following error might raise. SQL Error [23505]: ERROR: duplicate key value violates unique constraint "emps_iden_pkey" Detail: Key (emp_id)=(2) already exists.

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