# Oracle and PostgreSQL tables and indexes
Tables and indexes
This section provides reference pages for Oracle and PostgreSQL tables and indexes.
**Topics**
+ [
# Case sensitivity differences for Oracle and PostgreSQL
](chap-oracle-aurora-pg.tables.case.md)
+ [
# Common Oracle and PostgreSQL data types
](chap-oracle-aurora-pg.tables.common.md)
+ [
# Oracle read-only tables and partitions and PostgreSQL Aurora replicas
](chap-oracle-aurora-pg.tables.readonly.md)
+ [
# Oracle and PostgreSQL table constraints
](chap-oracle-aurora-pg.tables.constraints.md)
+ [
# Oracle and PostgreSQL temporary tables
](chap-oracle-aurora-pg.tables.temporary.md)
+ [
# Oracle triggers and PostgreSQL trigger procedure
](chap-oracle-aurora-pg.tables.triggers.md)
+ [
# Oracle and PostgreSQL tablespaces and data files
](chap-oracle-aurora-pg.tables.tablespaces.md)
+ [
# Oracle and PostgreSQL user-defined types
](chap-oracle-aurora-pg.tables.udt.md)
+ [
# Oracle unused columns and PostgreSQL ALTER TABLE statement
](chap-oracle-aurora-pg.tables.alter.md)
+ [
# Oracle virtual columns and PostgreSQL views and functions
](chap-oracle-aurora-pg.tables.virtual.md)
+ [
# Overall Oracle and PostgreSQL indexes summary
](chap-oracle-aurora-pg.tables.indexes.md)
+ [
# Oracle bitmap indexes and PostgreSQL bitmap
](chap-oracle-aurora-pg.tables.bitmap.md)
+ [
# Oracle and PostgreSQL B-tree indexes
](chap-oracle-aurora-pg.tables.btree.md)
+ [
# Oracle composite indexes and PostgreSQL multi-column indexes
](chap-oracle-aurora-pg.tables.composite.md)
+ [
# Oracle function-based indexes and PostgreSQL expression indexes
](chap-oracle-aurora-pg.tables.expression.md)
+ [
# Oracle and PostgreSQL invisible indexes
](chap-oracle-aurora-pg.tables.invisible.md)
+ [
# Oracle index-organized table and PostgreSQL cluster table
](chap-oracle-aurora-pg.tables.iot.md)
+ [
# Oracle local and global partitioned indexes and PostgreSQL partitioned indexes
](chap-oracle-aurora-pg.tables.partitioned.md)
+ [
# Oracle automatic indexing and self-managed PostgreSQL
](chap-oracle-aurora-pg.tables.autoindex.md)
# Case sensitivity differences for Oracle and PostgreSQL
Object name case sensitivity is different for Oracle and PostgreSQL. Oracle names aren’t case sensitive. PostgreSQL names are case sensitive.
By default, AWS SCT uses object name in lower-case for PostgreSQL. In most cases, you’ll want to use AWS DMS transformations to change schema, table, and column names to lower case.
To have an upper-case name, you must place the objects names within doubles quotes.
For example, to create a table named `EMPLOYEES` (upper-case) in PostgreSQL, you should use the following
```
CREATE TABLE "EMPLOYEES" (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FULL_NAME VARCHAR(60) NOT NULL,
AVG_SALARY NUMERIC NOT NULL);
```
The following command creates a table named employees (lower-case).
```
CREATE TABLE EMPLOYEES (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FULL_NAME VARCHAR(60) NOT NULL,
AVG_SALARY NUMERIC NOT NULL);
```
If you don’t use doubles quotes, PostgreSQL looks for object names in their lower-case form. For `CREATE` commands where you don’t use doubles quotes, PostgreSQL creates objects with lower-case names. Therefore, to create, query, or manipulate an upper-cased (or mixed) object names, use doubles quotes.
# Common Oracle and PostgreSQL data types
With AWS DMS, you can seamlessly migrate data between different database platforms, including Oracle and PostgreSQL, while ensuring data type compatibility. Common Oracle and PostgreSQL data types refer to the fundamental data structures used to store and represent various types of information within these database systems.
| 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) | [Data Types](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.datatypes) | PostgreSQL doesn’t support `BFILE`, `ROWID`, `UROWID`. |
## Oracle usage
Oracle provides a set of primitive data types for defining table columns and PL/SQL code variables. The assigned data types for table columns or PL/SQL code (such as stored procedures and triggers) define the valid values each column or argument can store.
## Oracle data types and PostgreSQL data types
**Character data types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| CHAR(n) | Maximum size of 2000 bytes | Yes | CHAR(n) |
| CHARACTER(n) | Maximum size of 2000 bytes | Yes | CHARACTER(n) |
| NCHAR(n) | Maximum size of 2000 bytes | No | CHAR(n) |
| VARCHAR(n) | Maximum size of 2000 bytes | Yes | VARCHAR(n) |
| NCHAR VARYING (n) | Varying-length UTF-8 string, maximum size of 4000 bytes | No | CHARACTER VARYING(n) |
| VARCHAR2(n) 11g | Maximum size of 4000 bytes. Maximum size of 32KB in PL/SQL. | No | VARCHAR(n) |
| VARCHAR2(n) 12g | Maximum size of 32767 bytes. MAX\$1STRING\$1SIZE=EXTENDED | No | VARCHAR(n) |
| NVARCHAR2(n) | Maximum size of 4000 bytes | No | VARCHAR(n) |
| LONG | Maximum size of 2GB | No | TEXT |
| RAW(n) | Maximum size of 2000 bytes | No | BYTEA |
| LONG RAW | Maximum size of 2GB | No | BYTEA |
**Numeric data types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| NUMBER | Floating-point number | No | DOUBLE PRECISION |
| NUMBER(\$1) | Floating-point number | No | DOUBLE PRECISION |
| NUMBER(p,s) | Precision can range from 1 to 38, scale can range from -84 to 127 | No | DECIMAL(p,s) |
| NUMERIC(p,s) | Precision can range from 1 to 38 | Yes | NUMERIC(p,s) |
| FLOAT(p) | Floating-point number | No | DOUBLE PRECISION |
| DEC(p,s) | Fixed-point number | Yes | DEC(p,s) |
| DECIMAL(p,s) | Fixed-point number | Yes | DECIMAL(p,s) |
| INT | 38 digits integer | Yes | INTEGER or NUMERIC(38,0) |
| INTEGER | 38 digits integer | Yes | INTEGER or NUMERIC(38,0) |
| SMALLINT | 38 digits integer | Yes | SMALLINT |
| REAL | Floating-point number | No | DOUBLE PRECISION |
| DOUBLE PRECISION | Floating-point number | Yes | DOUBLE PRECISION |
**Date and time data types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| DATE | DATE data type stores date and time data (year, month, day, hour, minute and second) | Yes | TIMESTAMP(0) |
| TIMESTAMP(p) | Date and time with fraction | Yes | TIMESTAMP(p) |
| TIMESTAMP(p) WITH TIME ZONE | Date and time with fraction and time zone | Yes | TIMESTAMP(p) WITH TIME ZONE |
| INTERVAL YEAR(p) TO MONTH | Date interval | Yes | INTERVAL YEAR TO MONTH |
| INTERVAL DAY(p) TO SECOND(s) | Day and time interval | Yes | INTERVAL DAY TO SECOND(s) |
**LOB data types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| BFILE | Pointer to binary file, maximum file size of 4 GB | No | VARCHAR (255) or CHARACTER VARYING (255) |
| BLOB | Binary large object, maximum file size of 4 GB | No | BYTEA |
| CLOB | Character large object, maximum file size of 4 GB | No | TEXT |
| NCLOB | Variable-length Unicode string, maximum file size of 4 GB | No | TEXT |
**ROWID data types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| ROWID | Physical row address | No | CHARACTER (255) |
| UROWID(n) | Universal row id, logical row addresses | No | CHARACTER VARYING |
**XML data type**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| XMLTYPE | XML data | No | XML |
**Logical data type**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| BOOLEAN | Values `TRUE`, `FALSE`, and `NULL`, can’t be assigned to a database table column | Yes | BOOLEAN |
**Spatial types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| SDO\$1GEOMETRY | The geometric description of a spatial object | No | N/A |
| SDO\$1TOPO\$1GEOMETRY | Describes a topology geometry | No | N/A |
| SDO\$1GEORASTER | A raster grid or image object is stored in a single row | No | N/A |
**Media types**
| Oracle data type | Oracle data type characteristic | PostgreSQL identical compatibility | PostgreSQL corresponding data type |
| --- | --- | --- | --- |
| ORDDicom | Supports the storage and management of audio data | No | N/A |
| ORDDicom | Supports the storage and management of Digital Imaging and Communications in Medicine (DICOM). | No | N/A |
| ORDDoc | Supports storage and management of any type of media data | No | N/A |
| ORDImage | Supports the storage and management of image data | No | N/A |
| ORDVideo | Supports the storage and management of video data | No | N/A |
**Note**
The “PostgreSQL identical compatibility” column indicates if you can use the exact Oracle data type syntax when migrating to Amazon Aurora PostgreSQL.
### Oracle character column semantics
Oracle supports both `BYTE` and `CHAR` semantics for column size, which determines the amount of storage allocated for `CHAR` and `VARCHAR` columns.
+ If you define a field as `VARCHAR2(10 BYTE)`, Oracle can use up to 10 bytes for storage. However, based on your database codepage and NLS settings, you may not be able to store 10 characters in that field because the physical size of some non-English characters exceeds one byte.
+ If you define a field as `VARCHAR2(10 CHAR)`, Oracle can store 10 characters no matter how many bytes are required to store each non-English character.
```
CREATE TABLE table1 (col1 VARCHAR2(10 CHAR), col2 VARCHAR2(10 BYTE));
```
By default, Oracle uses `BYTE` semantics. When using a multi-byte character set such as UTF8, use one of the following options.
+ Use the `CHAR` modifier in the `VARCHAR2` or `CHAR` column definition.
+ Modify the session or system parameter `NLS_LENGTH_SEMANTICS` to change the default from `BYTE` to `CHAR`.
```
ALTER system SET nls_length_semantics=char scope=both;
ALTER system SET nls_length_semantics=byte scope=both;
ALTER session SET nls_length_semantics=char;
ALTER session SET nls_length_semantics=byte;
```
For more information, see [Data Types](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL provides multiple data types equivalent to certain Oracle data types. The following table provides the full list of PostgreSQL data types.
**Character data types**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| CHAR | Stores a single character |
| CHARACTER | Stores a single character |
| CHAR(n) | Stores exactly (n) characters |
| VARCHAR(N) | Stores a variable number of characters, up to a maximum of n characters |
| TEXT | Specific variant of varchar, which doesn’t require you to specify an upper limit on the number of characters |
**Numeric data types**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| NUMERIC (P,S) | Exact numeric of selectable precision |
| REAL | Single precision floating-point number (4 bytes) |
| DOUBLE PRECISION | Double precision floating-point number (8 bytes) |
| INT | A signed 4-byte integer that can store from -2147483648 to \$12147483647 |
| INTEGER | A signed 4-byte integer that can store from -2147483648 to \$12147483647 |
| SMALLINT | A signed 2-byte integer that can store from -32768 to \$132767 |
| BIGINT | A signed 8-byte integer, giving approximately 18 digits of precision |
| BIT | Stores a single bit, 0 or 1 |
| BIT VARYING | Stores a string of bits |
| MONEY | Equivalent to NUMERIC (9,2), storing 4 bytes of data. Its use is discouraged. |
**Date and time data types**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| TIMESTAMP | Stores dates and times from 4713 BC to 1465001 AD, with a resolution of 1 microsecond - 8 bytes |
| INTERVAL | Stores an interval of approximately 178,000,000 years, with a resolution of 1 microsecond - 16 bytes |
| DATE | Stores dates from 4713 BC to 32767 AD, with a resolution of 1 day - 4 bytes |
| TIME | Stores a time of day, from 0 to 23:59:59.99, with a resolution of 1 microsecond - 8 bytes with no timezone, 12 bytes with timezone |
**Logical data type**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| BOOLEAN | Holds a truth value. Will accept values such as TRUE, 't','true', 'y', 'yes', and '1' as true. Uses 1 byte of storage, and can store NULL. You can use this type upon table creation. |
**XML data type**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| XML | XML data |
**Geometric data types**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| POINT | The column type to specify when you want to use the following data models |
| LINE | An (x,y) value |
| LSEG | A line (pt1, pt2) |
| BOX | A sequence of points, effectively a closed path |
| PATH | Collection of POINTs |
| POLYGON | Collection of LINEs |
| CIRCLE | Collection of POLYGONs |
**PotgreSQL data types**
| PostgreSQL data type | PostgreSQL data type characteristic |
| --- | --- |
| JSON | Textual JSON data |
| JSONB | Binary JSON data, decomposed |
| SERIAL | A numeric column in a table that increases each time a row is added |
| OID | An object identifier. Internally, PostgreSQL adds, by default, a hidden oid to each row, and stores a 4-byte integer. |
| CIDR | Stores a network address of the form x.x.x.x/y where y is the netmask |
| INET | Similar to cidr, except the host part can be 0 |
| MACADDR | MAC (Media Access Control) address |
| MACADDR8 | MAC (Media Access Control) address in EUI-64 format (PostgreSQL 10) |
| PG\$1LSN | PostgreSQL Log Sequence Number |
| BYTEA | Binary data ("byte array") |
| TSQUERY | Text search query |
| TSVECTOR | Text search document |
| TXID\$1SNAPSHOT | User-level transaction ID snapshot |
| UUID | Universally unique identifier |
### PostgreSQL character column semantics
PostgreSQL only supports `CHAR` for column size semantics. If you define a field as `VARCHAR (10)`, PostgreSQL can store 10 characters regardless of how many bytes it takes to store each non-English character. `VARCHAR(n)` stores strings up to n characters (not bytes) in length.
## Migration of Oracle data types to PostgreSQL data types
You can perform automatic migration and conversion of Oracle tables and data types using AWS Schema Conversion Tool (AWS SCT).
**Examples**
To demonstrate AWS SCT capability for migrating Oracle tables to their PostgreSQL equivalents, a table containing columns representing the majority of Oracle data types was created and converted using AWS SCT.
Source Oracle compatible DDL for creating the `DATATYPES` table.
```
CREATE TABLE "DATATYPES"(
"BFILE" BFILE,
"BINARY_FLOAT" BINARY_FLOAT,
"BINARY_DOUBLE" BINARY_DOUBLE,
"BLOB" BLOB,
"CHAR" CHAR(10 BYTE),
"CHARACTER" CHAR(10 BYTE),
"CLOB" CLOB,
"NCLOB" NCLOB,
"DATE" DATE,
"DECIMAL" NUMBER(3,2),
"DEC" NUMBER(3,2),
"DOUBLE_PRECISION" FLOAT(126),
"FLOAT" FLOAT(3),
"INTEGER" NUMBER(*,0),
"INT" NUMBER(*,0),
"INTERVAL_YEAR" INTERVAL YEAR(4) TO MONTH,
"INTERVAL_DAY" INTERVAL DAY(4) TO SECOND(4),
"LONG" LONG,
"NCHAR" NCHAR(10),
"NCHAR_VARYING" NVARCHAR2(10),
"NUMBER" NUMBER(9,9),
"NUMBER1" NUMBER(9,0),
"NUMBER(*)" NUMBER,
"NUMERIC" NUMBER(9,9),
"NVARCHAR2" NVARCHAR2(10),
"RAW" RAW(10),
"REAL" FLOAT(63),
"ROW_ID" ROWID,
"SMALLINT" NUMBER(*,0),
"TIMESTAMP" TIMESTAMP(5),
"TIMESTAMP_WITH_TIME_ZONE" TIMESTAMP(5) WITH TIME ZONE,
"UROWID" UROWID(10),
"VARCHAR" VARCHAR2(10 BYTE),
"VARCHAR2" VARCHAR2(10 BYTE),
"XMLTYPE" XMLTYPE
);
```
Target PostgreSQL compatible DDL for creating the `DATATYPES` table migrated from Oracle with AWS SCT.
```
CREATE TABLE IF NOT EXISTS datatypes(
bfile character varying(255) DEFAULT NULL,
binary_float real DEFAULT NULL,
binary_double double precision DEFAULT NULL,
blob bytea DEFAULT NULL,
char character(10) DEFAULT NULL,
character character(10) DEFAULT NULL,
clob text DEFAULT NULL,
nclob text DEFAULT NULL,
date TIMESTAMP(0) without time zone DEFAULT NULL,
decimal numeric(3,2) DEFAULT NULL,
dec numeric(3,2) DEFAULT NULL,
double_precision double precision DEFAULT NULL,
float double precision DEFAULT NULL,
integer numeric(38,0) DEFAULT NULL,
int numeric(38,0) DEFAULT NULL,
interval_year interval year to month(6) DEFAULT NULL,
interval_day interval day to second(4) DEFAULT NULL,
long text DEFAULT NULL,
nchar character(10) DEFAULT NULL,
nchar_varying character varying(10) DEFAULT NULL,
number numeric(9,9) DEFAULT NULL,
number1 numeric(9,0) DEFAULT NULL,
"number(*)" double precision DEFAULT NULL,
numeric numeric(9,9) DEFAULT NULL,
nvarchar2 character varying(10) DEFAULT NULL,
raw bytea DEFAULT NULL,
real double precision DEFAULT NULL,
row_id character(255) DEFAULT NULL,
smallint numeric(38,0) DEFAULT NULL,
timestamp TIMESTAMP(5) without time zone DEFAULT NULL,
timestamp_with_time_zone TIMESTAMP(5) with time zone DEFAULT NULL,
urowid character varying DEFAULT NULL,
varchar character varying(10) DEFAULT NULL,
varchar2 character varying(10) DEFAULT NULL,
xmltype xml DEFAULT NULL
)
WITH (
OIDS=FALSE
);
```
AWS SCT converted most of the datatypes. However, a few exceptions were raised for datatypes that AWS SCT is unable to automatically convert and where AWS SCT recommended manual actions.
**PostgreSQL doesn’t have a data type BFILE**
`BFILE`s are pointers to binary files.
*Recommended actions*: Either store a named file with the data and create a routine that gets that file from the file system, or store the data blob inside your database.
**PostgreSQL doesn’t have a data type ROWID**
`ROWID`s are physical row addresses inside Oracle storage subsystems. The `ROWID` datatype is primarily used for values returned by the `ROWID` pseudocolumn.
*Recommended actions*: While PostgreSQL contains a `ctid` column that is the physical location of the row version within its table, it doesn’t have a comparable data type. However, you can use `CHAR` as a partial datatype equivalent.
**Example**
If you use ROWID datatypes in your code, modifications may be necessary.
**PostgreSQL doesn’t have a data type UROWID**
Universal rowid, or `UROWID`, is a single Oracle datatype that supports both logical and physical rowids of foreign table rowids such as non-Oracle tables accessed through a gateway.
*Recommended actions*: PostgreSQL doesn’t have a comparable data type. You can use `VARCHAR(n)` as a partial datatype equivalent. However, if you are using `UROWID` datatypes in your code, modifications may be necessary.
For more information, see [System Columns](https://www.postgresql.org/docs/13/ddl-system-columns.html) and [Data Types](https://www.postgresql.org/docs/13/datatype.html) in the *PostgreSQL documentation*, and [What is the AWS Schema Conversion Tool?](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) in the *user guide*.
# Oracle read-only tables and partitions and PostgreSQL Aurora replicas
With AWS DMS, you can migrate data from Oracle databases to Amazon Aurora PostgreSQL-Compatible Edition with minimal downtime by leveraging Oracle read-only tables and partitions for ongoing replication, and PostgreSQL Aurora replicas for read scaling. Oracle read-only tables and partitions facilitate ongoing replication from an Oracle source database, while PostgreSQL Aurora replicas provide read scaling for the migrated Aurora PostgreSQL database.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | N/A | N/A |
## Oracle usage
Beginning with Oracle 11g, tables can be marked as read-only to prevent DML operations from altering table data.
Prior to Oracle 11g, the only way to set a table to read-only mode was by limiting table privileges to `SELECT`. The table owner was still able to perform read and write operations. Begining with Oracle 11g, users can run an `ALTER TABLE` statement and change the table mode to either `READ ONLY` or `READ WRITE`.
Oracle 12c Release 2 introduces greater granularity for read-only objects and supports read-only table partitions. Any attempt to perform a DML operation on a partition, or sub-partition, set to `READ ONLY` results in an error.
`SELECT FOR UPDATE` statements aren’t allowed.
DDL operations are permitted if they don’t modify table data.
Operations on indexes are allowed on tables set to `READ ONLY` mode.
**Examples**
```
CREATE TABLE EMP_READ_ONLY (
EMP_ID NUMBER PRIMARY KEY,
EMP_FULL_NAME VARCHAR2(60) NOT NULL);
INSERT INTO EMP_READ_ONLY VALUES(1, 'John Smith');
1 row created
ALTER TABLE EMP_READ_ONLY READ ONLY;
INSERT INTO EMP_READ_ONLY VALUES(2, 'Steven King');
ORA-12081: update operation not allowed on table "SCT"."TBL_READ_ONLY"
ALTER TABLE EMP_READ_ONLY READ WRITE;
INSERT INTO EMP_READ_ONLY VALUES(2, 'Steven King');
1 row created
COMMIT;
SELECT * FROM EMP_READ_ONLY;
EMP_ID EMP_FULL_NAME
1 John Smith
2 Steven King
```
For more information, see [ALTER TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html) and [Changes in This Release for Oracle Database VLDB and Partitioning Guide](https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/release-changes.html#GUID-C7A9BAD4-E4C9-4765-88C5-51AC7E97BAF1) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL doesn’t provide an equivalent to the `READ ONLY` mode supported in Oracle.
You can use the following alternatives as a workaround:
+ Read-only user or role.
+ Read-only database.
+ Creating a read-only database trigger or a using a read-only constraint.
**PostgreSQL read-only user or role example**
To achieve some degree of protection from unwanted DML operations on table for a specific database user, you can grant the user only the `SELECT` privilege on the table and set the user `default_transaction_read_only` parameter to `ON`.
Create a PostgreSQL user with `READ ONLY` privileges.
```
CREATE TABLE EMP_READ_ONLY (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FULL_NAME VARCHAR(60) NOT NULL);
CREATE USER aws_readonly PASSWORD 'aws_readonly';
CREATE ROLE
ALTER USER aws_readonly SET DEFAULT_TRANSACTION_READ_ONLY=ON;
ALTER ROLE
GRANT SELECT ON EMP_READ_ONLY TO aws_readonly;
GRANT
-- Open a new session with user “aws_readonly”
SELECT * FROM EMP_READ_ONLY;
emp_id emp_full_name
(0 rows)
INSERT INTO EMP_READ_ONLY VALUES(1, 'John Smith');
ERROR: can't execute INSERT in a read-only transaction
```
**PostgreSQL read-only database example**
As an alternative solution for restricting write operations on database objects, a dedicated read-only PostgreSQL database can be created to store all read-only tables. PostgreSQL supports multiple databases under the same database instance. Adding a dedicated “read-only” database is a simple and straightforward solution.
+ Set the `DEFAULT_TRANSACTION_READ_ONLY` to `ON` for a database. If a session attempts to perform DDL or DML operations, and error will be raised.
+ The database can be altered back to `READ WRITE` mode when the parameter is set to `OFF`.
Create a PostgreSQL READ ONLY database.
```
CREATE DATABASE readonly_db;
ALTER DATABASE readonly_db SET DEFAULT_TRANSACTION_READ_ONLY=ON;
-- Open a new session connected to the “readonly_db” database
CREATE TABLE EMP_READ_ONLY (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FULL_NAME VARCHAR(60) NOT NULL);
ERROR: can't execute CREATE TABLE in a read-only transaction
-- In case of an existing table
INSERT INTO EMP_READ_ONLY VALUES(1, 'John Smith');
ERROR: can't execute INSERT in a read-only transaction
```
**PostgreSQL read-only database trigger example**
You can create an `INSTEAD OF` trigger to prevent data modifications on a specific table, such as restricting `INSERT`, `UPDATE`, `DELETE` and `TRUNCATE`.
Create PostgreSQL function which contains the logic for restricting to read-only operations:
```
CREATE OR REPLACE FUNCTION READONLY_TRIGGER_FUNCTION()
RETURNS
TRIGGER AS $$
BEGIN
RAISE EXCEPTION 'THE "%" TABLE IS READ ONLY!', TG_TABLE_NAME
using hint = 'Operation Ignored';
RETURN NULL;
END;
$$ language 'plpgsql';
```
Create a trigger which will run the function that was previously created.
```
CREATE TRIGGER EMP_READONLY_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE
ON EMP_READ_ONLY FOR EACH STATEMENT
EXECUTE PROCEDURE READONLY_TRIGGER_FUNCTION();
```
Test DML and truncate commands against the table with the new trigger.
```
INSERT INTO EMP_READ_ONLY VALUES(1, 'John Smith');
ERROR: THE "EMP_READ_ONLY" TABLE IS READ ONLY!
HINT: Operation Ignored
CONTEXT: PL/pgSQL function readonly_trigger_function() line 3 at
RAISE
demo>= TRUNCATE TABLE SRC;
ERROR: THE " EMP_READ_ONLY" TABLE IS READ ONLY!
HINT: Operation Ignored
CONTEXT: PL/pgSQL function readonly_trigger_function() line 3 at
RAISE
```
For more information, see [Privileges](https://www.postgresql.org/docs/13/ddl-priv.html), [GRANT](https://www.postgresql.org/docs/13/sql-grant.html), and [Client Connection Defaults](https://www.postgresql.org/docs/13/runtime-config-client.html) in the *PostgreSQL documentation*.
# Oracle and PostgreSQL table constraints
With AWS DMS, you can migrate data from source databases while applying table constraints to enforce data integrity on the target PostgreSQL or Oracle databases. Table constraints define rules for the data in a table, such as restricting null values, ensuring unique entries, or specifying conditions for accepted values.
| 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) | [Creating Tables](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.tables) | PostgreSQL doesn’t support `REF`, `ENABLE`, and `DISABLE`. Also, PostgreSQL doesn’t support constraints on views. |
## Oracle usage
Oracle provides six types of constraints to enforce data integrity on table columns. Constraints ensure data inserted into tables is controlled and satisfies logical requirements.
### Oracle integrity constraint types
+ **Primary Key** — Enforces that row values in a specific column are unique and not null.
+ **Foreign Key** — Enforces that values in the current table exist in the referenced table.
+ **Unique** — Prevents data duplication on a column, or combination of columns, and allows one null value.
+ **Check** — Enforces that values comply with a specific condition.
+ **Not Null** — Enforces that null values can’t be inserted into a specific column.
+ **REF** — References an object in another object type or in a relational table.
### Oracle constraint creation
You can create new constraints in two ways.
1. **Inline** — Defines a constraint as part of a table column declaration.
```
CREATE TABLE EMPLOYEES (
EMP_ID NUMBER PRIMARY KEY,…);
```
1. **Out-of-line** — Defines a constraint as part of the table DDL during table creation.
```
CREATE TABLE EMPLOYEES (EMP_ID NUMBER,…,
CONSTRAINT PK_EMP_ID PRIMARY KEY(EMP_ID));
```
**Note**
Declare NOT NULL constraints using the inline method.
Use the following syntax to specify Oracle constraints:
+ `CREATE / ALTER TABLE`
+ `CREATE / ALTER VIEW`
**Note**
Views have only a primary key, foreign key, and unique constraints.
**Privileges**
You need privileges on the table where constrains are created and, in case of foreign key constraints, you need the `REFERENCES` privilege on the referenced table.
### PRIMARY KEY constraints
A unique identifier for each record in a database table can appear only once and can’t contain NULL values. A table can only have one primary key.
When creating a primary key constraint inline, you can specify only the `PRIMARY KEY` keyword. When you create the constraint out-of-line, you must specify one column or a combination of columns.
Creating a new primary key constraint also implicitly creates a unique index on the primary key column if no index already exists. When dropping a primary key constraint, the system-generated index is also dropped. If a user defined index was used, the index isn’t dropped.
+ Primary keys can’t be created on columns defined with the following data types: `LOB`, `LONG`, `LONG RAW`, `VARRAY`, `NESTED TABLE`, `BFILE`, `REF`, `TIMESTAMP WITH TIME ZONE`.
The TIMESTAMP WITH LOCAL TIME ZONE data type is allowed as a primary key.
+ Primary keys can be created from multiple columns (composite PK). They are limited to a total of 32 columns.
+ Defining the same column as both a primary key and as a unique constraint isn’t allowed.
**Examples**
Create an Inline primary key using a system-generated primary key constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25));
```
Create an inline primary key using a user-specified primary key constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER CONSTRAINT PK_EMP_ID PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25));
```
Create an out-of-line primary key.
```
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25));
CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID));
```
Add a primary key to an existing table.
```
ALTER TABLE SYSTEM_EVENTS
ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EVENT_CODE, EVENT_TIME);
```
### FOREIGN KEY constraints
Foreign key constraints identify the relationship between column records defined with a foreign key constraint and a referenced primary key or a unique column. The main purpose of a foreign key is to enforce that the values in table A also exist in table B as referenced by the foreign key.
A referenced table is known as a parent table. The table on which the foreign key was created is known as a child table. Foreign keys created in child tables generally reference a primary key constraint in a parent table.
**Limitations**
Foreign keys can’t be created on columns defined with the following data types: `LOB`, `LONG`, `LONG RAW`, `VARRAY`, `NESTED TABLE`, `BFILE`, `REF`, `TIMESTAMP WITH TIME ZONE`.
Composite Foreign key constraints comprised from multiple columns can’t have more than 32 columns.
Foreign key constraints can’t be created in a `CREATE TABLE` statement with a subquery clause.
A referenced primary key or unique constraint on a parent table must be created before the foreign key creation command.
**ON DELETE clause**
The `ON DELETE` clause specifies the effect of deleting values from a parent table on the referenced records of a child table. If the `ON DELETE` clause isn’t specified, Oracle doesn’t allow deletion of referenced key values in a parent table that has dependent rows in the child table.
+ `ON DELETE CASCADE` — Dependent foreign key values in a child table are removed along with the referenced values from the parent table.
+ `ON DELETE NULL` — Dependent foreign key values in a child table are updated to NULL.
**Examples**
Create an inline foreign key with a user-defined constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25) ,
DEPARTMENT_ID REFERENCES DEPARTMENTS(DEPARTMENT_ID));
```
Create an Out-Of-Line foreign key with a system-generated constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
DEPARTMENT_ID NUMBER,
CONSTRAINT FK_FEP_ID
FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));
```
Create a foreign key using the `ON DELETE CASCADE` clause.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
DEPARTMENT_ID NUMBER,
CONSTRAINT FK_FEP_ID
FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
ON DELETE CASCADE);
```
Add a foreign key to an existing table.
```
ALTER TABLE EMPLOYEES
ADD CONSTRAINT FK_FEP_ID
FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);
```
### UNIQUE constraints
A unique constraint is similar to a primary key constraint. It specifies that the values in a single column, or combination of columns, must be unique and can’t repeat in multiple rows.
The main difference from primary key constraint is that a unique constraint can contain NULL values. NULL values in multiple rows are also supported provided the combination of values is unique.
**Limitations**
A unique constraint can’t be created on columns defined with the following data types: `LOB`, `LONG`, `LONG RAW`, `VARRAY`, `NESTED TABLE`, `BFILE`, `REF`, `TIMESTAMP WITH TIME ZONE`.
A unique constraint comprised from multiple columns can’t have more than 32 columns.
Primary key and unique constraints can’t be created on the same column or columns.
**Example**
Create an inline unique Constraint.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25) CONSTRAINT UNIQ_EMP_EMAIL UNIQUE,
DEPARTMENT_ID NUMBER);
```
### Check constraints
Check constraints are used to validate values in specific columns that meet specific criteria or conditions. For example, you can use a check constraint on an `EMPLOYEE_EMAIL` column to validate that each record has an @aws.com suffix. If a record fails the check validation, an error is raised and the record isn’t inserted.
Using a check constraint can help transfer some of the logical integrity validation from the application to the database.
When creating a check constraint as inline, it can only be defined on a specific column. When using the out-of-line method, the check constraint can be defined on multiple columns.
**Limitations**
Check constraints can’t perform validation on columns of other tables.
Check constraints can’t be used with functions that aren’t deterministic (e.g. `CURRENT_DATE`).
Check constraints can’t be used with user-defined functions.
Check constrains can’t be used with pseudo columns such as: `CURRVAL`, `NEXTVAL`, `LEVEL`, or `ROWNUM`.
**Example**
Create an inline check constraint that uses a regular expression to validate the email suffix of inserted rows contains @aws.com.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25)
CHECK(REGEXP_LIKE (EMAIL, '^[A-Za-z]+@aws.com?{1,3}$')),
DEPARTMENT_ID NUMBER);
```
### Not Null constraints
A Not Null constraint prevents a column from containing any null values. In order to enable the not null constraint, the keywords `NOT NULL` must be specified during table creation (inline only). Permitting null values is the default if `NOT NULL` isn’t specified.
**Example**
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25),
DEPARTMENT_ID NUMBER);
```
### REF constraints
REF constraints define a relationship between a column of type REF and the object it references. The REF constraint can be created both inline and out-of-line. Both methods permit defining a scope constraint, a rowid constraint, or a referential integrity constraint based on the REF column.
**Examples**
Create a new Oracle type object.
```
CREATE TYPE DEP_TYPE AS OBJECT (
DEP_NAME VARCHAR2(60),
DEP_ADDRESS VARCHAR2(300));
```
Create a table based on the previously created type object.
```
CREATE TABLE DEPARTMENTS_OBJ_T OF DEP_TYPE;
```
Create the `EMPLOYEES` table with a reference to the previously created `DEPARTMENTS` table that is based on the `DEP_TYPE` object:
```
CREATE TABLE EMPLOYEES (
EMP_NAME VARCHAR2(60),
EMP_EMAIL VARCHAR2(60),
EMP_DEPT REF DEPARTMENT_TYP REFERENCES DEPARTMENTS_OBJ_T);
```
### Special constraint states
Oracle provides granular control of database constraint enforcement. For example, you can disable constraints temporarily while making modifications to table data.
Constraint states can be defined using the `CREATE TABLE` or `ALTER TABLE` statements. The following constraint states are supported:
+ `DEFERRABLE` — Enables the use of the `SET CONSTRAINT` clause in subsequent transactions until a `COMMIT` statement is submitted.
+ `NOT DEFERRABLE` — Disables the use of the `SET CONSTRAINT` clause.
+ `INITIALLY IMMEDIATE` — Checks the constraint at the end of each subsequent SQL statement (this state is the default).
+ `INITIALLY DEFERRED` — Checks the constraint at the end of subsequent transactions.
+ `VALIDATE` or `NO VALIDATE` — These parameters depend on whether the constraint is `ENABLED` or `DISABLED`.
+ `ENABLE` or `DISABLE` — Specifies if the constraint should be enforced after creation (`ENABLE` by default). Several options are available when using `ENABLE` or `DISABLE`:
+ `ENABLE VALIDATE` — Enforces that the constraint applies to all existing and new data.
+ `ENABLE NOVALIDATE` — Only new data complies with the constraint.
+ `DISABLE VALIDATE` — A valid constraint is created in disabled mode with no index.
+ `DISABLE NOVALIDATE` — The constraint is created in disabled mode without validation of new or existing data.
**Examples**
Create a unique constraint with a state of `DEFERRABLE`.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25) CONSTRAINT UNIQ_EMP_EMAIL UNIQUE DEFERRABLE,
DEPARTMENT_ID NUMBER);
```
Modify the state of the constraint to `ENABLE NOVALIDATE`.
```
ALTER TABLE EMPLOYEES
ADD CONSTRAINT CHK_EMP_NAME CHECK(FIRST_NAME LIKE 'a%')
ENABLE NOVALIDATE;
```
### Using existing indexes to enforce constraint integrity
Primary key and unique constraints can be created based on an existing index to enforce the constraint integrity instead of implicitly creating a new index during constraint creation.
**Example**
Create a unique constraint based on an existing index.
```
CREATE UNIQUE INDEX IDX_EMP_ID ON EMPLOYEES(EMPLOYEE_ID);
ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_CON_UNIQ
PRIMARY KEY(EMPLOYEE_ID) USING INDEX IDX_EMP_ID;
```
For more information, see [CREATE TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL supports the following types of table constraints:
+ PRIMARY KEY
+ FOREIGN KEY
+ UNIQUE
+ NOT NULL
+ EXCLUDE (unique to PostgreSQL)
**Note**
PostgreSQL doesn’t support Oracle `REF` constraint.
Similar to constraint declaration in Oracle, in PostgreSQL you can create constraints in-line or out-of-line when you specify table columns.
You can specify PostgreSQL constraints using `CREATE` or `ALTER TABLE`. Views aren’t supported.
You need privileges on the table in which constrains are created. For foreign key constraints, you need the `REFERENCES` privilege.
### Primary key constraints
Primary key constraints uniquely identify each record and can’t contain a NULL value. You can use the same ANSI SQL syntax as Oracle.
You can create primary key constraints on a single column or on multiple columns (composite primary keys) as the only `PRIMARY KEY` in a table.
Create a `PRIMARY KEY` constraint creates a unique B-Tree index automatically on the column or group of columns marked as the primary key of the table.
Constraint names can be generated automatically by PostgreSQL or explicitly specified during constraint creation.
**Examples**
Create an inline primary key constraint with a system-generated constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25));
```
Create an inline primary key constraint with a user-specified constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC CONSTRAINT PK_EMP_ID PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25));
```
Create an out-of-line primary key constraint.
```
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID NUMERIC,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25));
CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID));
```
Add a primary key constraint to an existing table.
```
ALTER TABLE SYSTEM_EVENTS
ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EVENT_CODE, EVENT_TIME);
```
Drop the primary key.
```
ALTER TABLE SYSTEM_EVENTS DROP CONSTRAINT PK_EMP_ID;
```
### Foreign key constraints
Foreign key constraints enforces referential integrity in the database. Values in specific columns or group of columns must match the values from another table (or column).
To create a `FOREIGN KEY` constraint in PostgreSQL, use the same ANSI SQL syntax as in Oracle. You can create a foreign key constraint in-line or out-of-line during table creation.
Use the `REFERENCES` clause to specify the table referenced by the foreign key constraint. When you specify `REFERENCES` in absence of a column list in the referenced table, the `PRIMARY KEY` of the referenced table is used as the referenced column or columns.
A table can have multiple `FOREIGN KEY` constraints to describe its relationships with other tables.
Use the `ON DELETE` clause to handle cases of `FOREIGN KEY` parent records deletions (such as cascading deletes).
Foreign key constraint names are generated automatically by the database or specified explicitly during constraint creation.
### Foreign key and the ON DELETE clause
PostgreSQL provides three main options to handle cases where data is deleted from the parent table and a child table is referenced by a `FOREIGN KEY` constraint. By default, without specifying any additional options, PostgreSQL will use the `NO ACTION` method and raise an error if the referencing rows still exist when the constraint is verified.
+ `ON DELETE CASCADE` — Any dependent foreign key values in the child table are removed along with the referenced values from the parent table.
+ `ON DELETE RESTRICT` — Prevents the deletion of referenced values from the parent table and the deletion of dependent foreign key values in the child table.
+ `ON DELETE NO ACTION` — Performs no action (the default action). The fundamental difference between `RESTRICT` and `NO ACTION` is that `NO ACTION` allows the check to be postponed until later in the transaction; `RESTRICT` doesn’t.
### Foreign key and the ON UPDATE clause
Handling updates on `FOREIGN KEY` columns is also available using the `ON UPDATE` clause, which shares the same options as the `ON DELETE` clause:
+ `ON UPDATE CASCADE`
+ `ON UPDATE RESTRICT`
+ `ON UPDATE NO ACTION`
**Note**
Oracle doesn’t provide an `ON UPDATE` clause.
**Examples**
Create an inline foreign key with a user-specified constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25),
DEPARTMENT_ID NUMERIC REFERENCES DEPARTMENTS(DEPARTMENT_ID));
```
PostgreSQL foreign key columns must have a specified data type while Oracle doesn’t.
Create an out-of-line foreign key constraint with a system-generated constraint name.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25),
DEPARTMENT_ID NUMERIC,
CONSTRAINT FK_FEP_ID
FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));
```
Create a foreign key using the `ON DELETE CASCADE` clause.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25),
DEPARTMENT_ID NUMERIC,
CONSTRAINT FK_FEP_ID
FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
ON DELETE CASCADE);
```
Add a foreign key to an existing table.
```
ALTER TABLE EMPLOYEES
ADD CONSTRAINT FK_FEP_ID
FOREIGN KEY(DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID);
```
### UNIQUE constraints
`UNIQUE` constraints ensures that a value in a column, or a group of columns, is unique across the entire table. PostgreSQL `UNIQUE` constraint syntax is ANSI SQL compatible.
PostgreSQL automatically creates a B-Tree index on the respective column, or a group of columns, when creating a `UNIQUE` constraint.
If duplicate values exist in the column(s) on which the constraint was defined during `UNIQUE` constraint creation, the `UNIQUE` constraint creation fails, returning an error message.
`UNIQUE` constraints in PostgreSQL will accept multiple NULL values (similar to Oracle). `UNIQUE` constraint naming can be system-generated or explicitly specified.
**Example**
Create an inline unique constraint ensuring uniqueness of values in the email column.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25) CONSTRAINT UNIQ_EMP_EMAIL UNIQUE,
DEPARTMENT_ID NUMERIC);
```
### CHECK constraints
`CHECK` constraints enforce that values in a column satisfy a specific requirement. `CHECK` constraints in PostgreSQL use the same ANSI SQL syntax as Oracle.
You can only define `CHECK` constraints using a Boolean data type to evaluate the values of a column.
`CHECK` constraints naming can be system-generated or explicitly specified by the user during constraint creation.
**Example**
Create an inline `CHECK` constraint, using a regular expression, to enforce that the email column contains email addresses with the @aws.com suffix.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25) CHECK(EMAIL ~ '(^[A-Za-z]+@aws.com$)'),
DEPARTMENT_ID NUMERIC);
```
### NOT NULL constraints
`NOT NULL` constraints enforce that a column can’t accept NULL values. This behavior is different from the default column behavior in PostgreSQL where columns can accept NULL values. `NOT NULL` constraints can only be defined inline, during table creation (similar to Oracle).
`NOT NULL` constraints in PostgreSQL use the same ANSI SQL syntax as Oracle. You can explicitly specify names for `NOT NULL` constraints when used with a CHECK constraint.
**Example**
Define two not null constraints on the `FIRST_NAME` and `LAST_NAME` columns. Define a check constraint (with an explicitly user-specified name) to enforce not null behavior on the `EMAIL` column.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
EMAIL VARCHAR(25) CONSTRAINT CHK_EMAIL
CHECK(EMAIL IS NOT NULL));
```
### Constraint states
Similarly to Oracle, PostgreSQL provides controls for certain aspects of constraint behavior. Using the PostgreSQL `SET CONSTRAINTS` statement, constraints can be defined as.
+ `DEFERRABLE` — Allows you to use the `SET CONSTRAINTS` statement to set the behavior of constraint checking within the current transaction until transaction commit.
+ `IMMEDIATE` — Constraints are enforced only at the end of each statement. Each constraint has its own `IMMEDIATE` or `DEFERRED` mode (same as Oracle)
+ `NOT DEFERRABLE` — This statement always runs as `IMMEDIATE` and isn’t affected by the `SET CONSTRAINTS` command.
**PostgreSQL SET CONSTRAINTS Synopsis**
```
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
```
+ `VALIDATE CONSTRAINT` — Validates foreign key or check constraints (only) that were previously created as NOT VALID. This action performs a validation check by scanning the table to ensure that all records satisfy the constraint definition.
+ `NOT VALID` — Can be used only for foreign key or check constraints. When specified, new records aren’t validated with the creation of the constraint. Only when the `VALIDATE CONSTRAINT` state is applied does the constraint state is enforced on all records.
**Example**
```
ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_DEPT
FOREIGN KEY (department_id)
REFERENCES DEPARTMENTS (department_id) NOT VALID;
ALTER TABLE EMPLOYEES VALIDATE CONSTRAINT FK_DEPT;
```
**Using Existing Indexes During Constraint Creation**
PostgreSQL can add a new primary key or unique constraints based on an existing unique index. All the index columns are included in the constraint. When creating constraints using this method, the index is owned by the constraint. When dropping the constraint, the index is also dropped.
**Example**
Use an existing unique index to create a primary key constraint.
```
CREATE UNIQUE INDEX IDX_EMP_ID ON EMPLOYEES(EMPLOYEE_ID);
ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_CON_UNIQ PRIMARY KEY USING INDEX IDX_EMP_ID;
```
### Summary
| Oracle constraint or parameter | PostgreSQL constraint or parameter |
| --- | --- |
| PRIMARY KEY | PRIMARY KEY |
| FOREIGN KEY | FOREIGN KEY |
| UNIQUE | UNIQUE |
| CHECK | CHECK |
| NOT NULL | NOT NULL |
| REF | Not Supported |
| DEFERRABLE | DEFERRABLE |
| NOT DEFERRABLE | NOT DEFERRABLE |
| SET CONSTRAINTS | SET CONSTRAINTS |
| INITIALLY IMMEDIATE | INITIALLY IMMEDIATE |
| INITIALLY DEFERRED | INITIALLY DEFERRED |
| ENABLE | Default, not supported as keyword |
| DISBALE | Not supported as keyword, NOT VALID can use instead |
| ENABLE VALIDATE | Default, not supported as keyword |
| ENABLE NOVALIDATE | NOT VALID |
| DISABLE VALIDATE | Not supported |
| DISABLE NOVALIDATE | Not supported |
| USING\$1INDEX\$1CLAUSE | table\$1constraint\$1using\$1index |
| View Constraints | Not supported |
| Metadata: DBA\$1CONSTRAINTS | Metadata: PG\$1CONSTRAINT |
For more information, see [Constraints](https://www.postgresql.org/docs/13/ddl-constraints.html), [SET CONSTRAINTS](https://www.postgresql.org/docs/13/sql-set-constraints.html), and [ALTER TABLE](https://www.postgresql.org/docs/13/sql-altertable.html) in the *PostgreSQL documentation*.
# Oracle and PostgreSQL temporary tables
With AWS DMS, you can efficiently migrate data between Oracle and PostgreSQL databases while leveraging temporary tables. Temporary tables are database objects that store data temporarily, existing only for the duration of a session or transaction. These tables are useful when you need to store intermediate results during complex queries or data transformations.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | [Creating Tables](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.tables) | PostgreSQL doesn’t support GLOBAL temporary table. PostgreSQL can’t read from multiple sessions. PostgreSQL drops tables after the session ends. |
## Oracle usage
In Oracle, you can create temporary tables for storing data that exists only for the duration of a session or transaction.
Use the `CREATE GLOBAL TEMPORARY TABLE` statement to create a temporary table. This type of table has a persistent DDL structure, but not persistent data. It doesn’t generate redo during DML. Two of the primary use-cases for temporary tables include:
+ Processing many rows as part of a batch operation while requiring staging tables to store intermediate results.
+ Storing data required only for the duration of a specific session. When the session ends, the session data is cleared.
When using temporary tables, the data is visible only to the session that inserts the data into the table.
Oracle 18c introduces private temporary tables which are temporary tables that are only available during session or transaction. After session or transaction ends they are automatically dropped.
### Oracle global temporary tables
Global temporary tables store data in the Oracle temporary tablespace.
DDL operations on a temporary table are permitted including `ALTER TABLE`, `DROP TABLE`, and `CREATE INDEX`.
Temporary tables can’t be partitioned, clustered, or created as index-organized tables. Also, they don’t support parallel `UPDATE`, `DELETE`, and `MERGE`.
Foreign key constraints can’t be created on temporary tables.
Processing DML operations on a temporary table doesn’t generate redo data. However, undo data for the rows and redo data for the undo sata itself are generated.
Indexes can be created for a temporary table. They are treated as temporary indexes. Temporary tables also support triggers.
Temporary tables can’t be named after an existing table object and can’t be dropped while containing records, even from another session.
### Session-specific and transaction-specific temporary table syntax
Use `ON COMMIT` to specifies whether the temporary table data persists for the duration of a transaction or a session.
Use `PRESERVE ROWS` when the session ends, all data is truncated but persists beyond the end of the transaction.
Use `DELETE ROWS` to truncate data after each commit. This is the default behavior.
### Oracle 12c temporary table enhancements
**Global temporary table statistics**
Prior to Oracle 12c, statistics on temporary tables were common to all sessions. Oracle 12c introduces sessionspecific statistics for temporary tables. Statistics can be configured using the `DBMS_STATS` preference `GLOBAL_TEMP_TABLE_STATS`, which can be set to `SHARED` or `SESSION`.
**Global temporary table undo**
Performing DML operations on a temporary table doesn’t generate Redo data, but does generate undo data that eventually, by itself, generates redo records. Oracle 12c provides an option to store the temporary undo data in the temporary tablespace itself. This feature is configured using the `temp_undo_enabled` parameter with the options `TRUE` or `FALSE`.
For more information, see [TEMP\$1UNDO\$1ENABLED](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/TEMP_UNDO_ENABLED.html#GUID-E2A01A84-2D63-401F-B64E-C96B18C5DCA6) in the *Oracle documentation*.
**Examples**
Create an Oracle global temporary table (with `ON COMMIT PRESERVE ROWS`).
```
CREATE GLOBAL TEMPORARY TABLE EMP_TEMP (
EMP_ID NUMBER PRIMARY KEY,
EMP_FULL_NAME VARCHAR2(60) NOT NULL,
AVG_SALARY NUMERIC NOT NULL)
ON COMMIT PRESERVE ROWS;
CREATE INDEX IDX_EMP_TEMP_FN ON EMP_TEMP(EMP_FULL_NAME);
INSERT INTO EMP_TEMP VALUES(1, 'John Smith', '5000');
COMMIT;
SELECT * FROM SCT.EMP_TEMP;
EMP_ID EMP_FULL_NAME AVG_SALARY
1 John Smith 5000
```
Create an Oracle global temporary table (with `ON COMMIT DELETE ROWS`).
```
CREATE GLOBAL TEMPORARY TABLE EMP_TEMP (
EMP_ID NUMBER PRIMARY KEY,
EMP_FULL_NAME VARCHAR2(60) NOT NULL,
AVG_SALARY NUMERIC NOT NULL)
ON COMMIT DELETE ROWS;
INSERT INTO EMP_TEMP VALUES(1, 'John Smith', '5000');
COMMIT;
SELECT * FROM SCT.EMP_TEMP;
```
For more information, see [CREATE TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/TEMP_UNDO_ENABLED.html#GUID-E2A01A84-2D63-401F-B64E-C96B18C5DCA6) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL temporary tables share many similarities with Oracle global temporary tables.
From a syntax perspective, PostgreSQL temporary tables are referred to as temporary tables without global definition. The implementation is mostly identical.
Starting from PostgreSQL 10, partition tables can also be temporary tables.
In terms of differences, Oracle stores the temporary table structure (DDL) for repeated use — even after a database restart — but doesn’t store rows persistently. PostgreSQL implements temporary tables differently: the table structure (DDL) isn’t stored in the database. When a session ends, the temporary table is dropped.
+ **Session-specific** — In PostgreSQL, every session is required to create its own Temporary Tables. Each session can create its own “private” Temporary Tables, using identical table names.
+ **LOCAL / GLOBAL syntax** — PostgreSQL temporary tables don’t support cross-session data access. PostgreSQL doesn’t distinguish between “GLOBAL” and “LOCAL” temporary tables. The use of these keywords is permitted in PostgreSQL, but they have no effect because PostgreSQL creates temporary tables as local and session-isolated tables.
**Note**
Use of the GLOBAL keyword is deprecated.
+ In the Oracle Database, the default behavior when the `ON COMMIT` clause is omitted is `ON COMMIT DELETE ROWS`. In PostgreSQL, the default is `ON COMMIT PRESERVE ROWS`.
### PostgreSQL temporary tables ON COMMIT clause
The `ON COMMIT` clause specifies the state of the data as it persists for the duration of a transaction or a session.
+ `PRESERVE ROWS` — The PostgreSQL default. When a session ends, all data is truncated but persists beyond the end of thetransaction.
+ `DELETE ROWS` — The data is truncated after each commit.
**Examples**
Create a use a temporary table, with `ON COMMIT PRESERVE ROWS`.
```
CREATE GLOBAL TEMPORARY TABLE EMP_TEMP (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FULL_NAME VARCHAR(60) NOT NULL,
AVG_SALARY NUMERIC NOT NULL)
ON COMMIT PRESERVE ROWS;
CREATE INDEX IDX_EMP_TEMP_FN ON EMP_TEMP(EMP_FULL_NAME);
INSERT INTO EMP_TEMP VALUES(1, 'John Smith', '5000');
COMMIT;
SELECT * FROM SCT.EMP_TEMP;
emp_id emp_full_name avg_salary
1 John Smith 5000
DROP TABLE EMP_TEMP;
```
Create and use a Temporary Table, with `ON COMMIT DELETE ROWS`.
```
CREATE GLOBAL TEMPORARY TABLE EMP_TEMP (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FULL_NAME VARCHAR(60) NOT NULL,
AVG_SALARY NUMERIC NOT NULL)
ON COMMIT DELETE ROWS;
INSERT INTO EMP_TEMP VALUES(1, 'John Smith', '5000');
COMMIT;
SELECT * FROM SCT.EMP_TEMP;
emp_id emp_full_name avg_salary
(0 rows)
DROP TABLE EMP_TEMP;
DROP TABLE
```
## Summary
| Feature | Oracle | Aurora PostgreSQL |
| --- | --- | --- |
| Semantic | Global Temporary Table | Temporary Table / Temp Table |
| Create table | CREATE GLOBAL TEMPORARY… | CREATE TEMPORARY… or CREATE TEMP… |
| Accessible from multiple sessions | Yes | No |
| Temp table DDL persist after session end / database restart usermanaged datafiles | Yes | No (dropped at the end of the session) |
| Create index support | Yes | Yes |
| Foreign key support | Yes | Yes |
| ON COMMIT default | COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS |
| ON COMMIT PRESERVE ROWS | Yes | Yes |
| ON COMMIT DELETE ROWS | Yes | Yes |
| Alter table support | Yes | Yes |
| Gather statistics | dbms\$1stats.gather\$1table\$1stats | ANALYZE |
| Oracle 12c GLOBAL\$1TEMP\$1TABLE\$1STATS | dbms\$1stats.set\$1table\$1prefs | ANALYZE |
For more information, see [CREATE TABLE](https://www.postgresql.org/docs/13/sql-createtable.html) in the *PostgreSQL documentation*.
# Oracle triggers and PostgreSQL trigger procedure
With AWS DMS, you can migrate databases to AWS while replicating database code objects, such as triggers across source and target databases. Triggers are database objects that automatically run a defined procedure when an event occurs, such as inserting, updating, or deleting data in a table. Oracle triggers and PostgreSQL trigger procedures define the logic and actions to be performed when specific events occur in the database.
| 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) | ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | [Triggers](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.triggers) | Different paradigm and syntax. System triggers aren’t supported by PostgreSQL. |
## Oracle usage
A trigger is a procedure that is stored in the database and fired when a specified event occurs. The associated event causing a trigger to run can either be tied to a specific database table, database view, database schema, or the database itself.
Triggers can be run after:
+ Data Manipulation Language (DML) statements such as `DELETE`, `INSERT`, or `UPDATE`.
+ Data Definition Language (DDL) statements such as `CREATE`, `ALTER`, or `DROP`.
+ Database events and operations such as `SERVERERROR`, `LOGON`, `LOGOFF`, `STARTUP`, or `SHUTDOWN`.
### Trigger types
+ **DML** triggers can be created on tables or views and fire when inserting, updating, or deleting data. Triggers can fire before or after DML command run.
+ **INSTEAD OF** triggers can be created on a non-editable view. `INSTEAD OF` triggers provide an application-transparent method for modifying views that can’t be modified by DML statements.
+ **SYSTEM Event** triggers are defined at the database or schema level including triggers that fire after specific events:
+ User log-on and log-off.
+ Database events (startup/shutdown), DataGuard events, server errors.
**Examples**
Create a trigger that runs after a row is deleted from the `PROJECTS` table, or if the primary key of a project is updated.
```
CREATE OR REPLACE TRIGGER PROJECTS_SET_NULL
AFTER DELETE OR UPDATE OF PROJECTNO ON PROJECTS
FOR EACH ROW
BEGIN
IF UPDATING AND :OLD.PROJECTNO != :NEW.PROJECTNO OR DELETING THEN
UPDATE EMP SET EMP.PROJECTNO = NULL
WHERE EMP.PROJECTNO = :OLD.PROJECTNO;
END IF;
END;
/
Trigger created.
DELETE FROM PROJECTS WHERE PROJECTNO=123;
SELECT PROJECTNO FROM EMP WHERE PROJECTNO=123;
PROJECTNO
NULL
```
Create a SYSTEM/Schema trigger on a table. The trigger fires if a `DDL DROP` command runs for an object in the `HR` schema. It prevents dropping the object and raises an application error.
```
CREATE OR REPLACE TRIGGER PREVENT_DROP_TRIGGER
BEFORE DROP ON HR.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (num => -20000,
msg => 'Cannot drop object');
END;
/
Trigger created.
DROP TABLE HR.EMP
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot drop object
ORA-06512: at line 2
```
For more information, see [CREATE TRIGGER Statement](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/CREATE-TRIGGER-statement.html#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B) in the *Oracle documentation*.
## PostgreSQL usage
A trigger is a procedure that is stored in the database and fired when a specified event occurs. DML triggers in PostgreSQL share much of the functionality that exists in Oracle triggers.
+ DML triggers (triggers that fire based on table related events such as DML).
+ Event triggers (triggers that fire after certain database events such as running DDL commands).
Unlike Oracle triggers, PostgreSQL triggers must call a function and don’t support anonymous blocks of PL/pgSQL code as part of the trigger body. The user-supplied function is declared with no arguments and has a return type of trigger.
**PostgreSQL CREATE TRIGGER synopsis**
```
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]}
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
```
**Note**
REFERENCING is a new option introduced in PostgreSQL 10. You can use this option with the `AFTER` trigger to interact with the overall view of the `OLD` or the `NEW TABLE` changed rows.
There are some cases that can fire multiple triggers numerous times. This includes triggers that aren’t planned to run, such as:
+ An `INSERT` with an `ON CONFLICT DO UPDATE` clause may cause both insert and update operations to fire.
+ `UPDATE` or `DELETE` caused by foreign-key enforcement can fire triggers. For example, `ON UPDATE CASCADE` or `ON DELETE SET NULL` can fire triggers that are supposed to fire on `UPDATE` or `DELETE` commands on the table.
### PostgreSQL DML triggers
PostgreSQL triggers can run `BEFORE` or `AFTER` a DML operation.
+ Fire before the operation is attempted on a row.
+ Before constraints are checked and the `INSERT`, `UPDATE`, or `DELETE` is attempted.
+ If the trigger fires before or instead of the event, the trigger can skip the operation for the current row or change the row being inserted (for `INSERT` and `UPDATE` operations only).
+ After the operation was completed, after constraints are checked and the `INSERT`, `UPDATE`, or `DELETE` command completed. If the trigger fires after the event, all changes, including the effects of other triggers, are visible to the trigger.
PostgreSQL triggers can run `INSTEAD OF` a DML command when created on views.
PostgreSQL triggers can run `FOR EACH ROW` affected by the DML statement or `FOR EACH STATEMENT` running only once as part of a DML statement.
| When fired | Database event | Row-level trigger | Statement-level trigger |
| --- | --- | --- | --- |
| BEFORE | INSERT, UPDATE, DELETE | Tables and foreign tables | Tables, views, and foreign tables |
| BEFORE | TRUNCATE | N/A | Tables |
| AFTER | INSERT, UPDATE, DELETE | Tables and foreign tables | Tables, views, and foreign tables |
| AFTER | TRUNCATE | N/A | Tables |
| INSTEAD OF | INSERT, UPDATE, DELETE | Views | N/A |
| INSTEAD OF | TRUNCATE | N/A | N/A |
### PostgreSQL event triggers
An event trigger runs when a specific event that is associated with the trigger occurs in the database. Supported events include: `ddl_command_start`, `ddl_command_end`, `table_rewrite` and `sql_drop`.
+ `ddl_command_start` occurs before the run of a `CREATE`, `ALTER`, `DROP`, `SECURITY LABEL`, `COMMENT`, `GRANT`, `REVOKE`, or `SELECT INTO` command.
+ `ddl_command_end` occurs after the command completed and before the transaction commits.
+ `sql_drop` fired only for the DROP DDL command. Fires before `ddl_command_end` trigger fire.
For more information, see [Event Trigger Firing Matrix](https://www.postgresql.org/docs/13/event-trigger-matrix.html) in the *PostgreSQL documentation*.
**Examples**
Create a DML trigger. To create an equivalent version of the Oracle DML trigger in PostgreSQL, first create a function trigger which will store the run logic for the trigger.
```
CREATE OR REPLACE FUNCTION PROJECTS_SET_NULL()
RETURNS TRIGGER
AS $$
BEGIN
IF TG_OP = 'UPDATE' AND OLD.PROJECTNO != NEW.PROJECTNO OR
TG_OP = 'DELETE' THEN
UPDATE EMP
SET PROJECTNO = NULL
WHERE EMP.PROJECTNO = OLD.PROJECTNO;
END IF;
IF TG_OP = 'UPDATE' THEN RETURN NULL;
ELSIF TG_OP = 'DELETE' THEN RETURN NULL;
END IF;
END; $$
LANGUAGE PLPGSQL;
```
Create the trigger.
```
CREATE TRIGGER TRG_PROJECTS_SET_NULL
AFTER UPDATE OF PROJECTNO OR DELETE
ON PROJECTS
FOR EACH ROW
EXECUTE PROCEDURE PROJECTS_SET_NULL();
CREATE TRIGGER
```
Test the trigger by deleting a row from the PROJECTS table.
```
DELETE FROM PROJECTS WHERE PROJECTNO=123;
SELECT PROJECTNO FROM EMP WHERE PROJECTNO=123;
projectno
(0 rows)
```
Create a DDL trigger that is an equivalent version of the Oracle DDL System/Schema level triggers (such as a trigger that prevent running a DDL DROP on objects in the HR schema).
Create an event trigger function.
Note that trigger functions are created with no arguments and must have a return type of `TRIGGER` or `EVENT_TRIGGER`.
```
CREATE OR REPLACE FUNCTION ABORT_DROP_COMMAND()
RETURNS EVENT_TRIGGER
AS $$
BEGIN
RAISE EXCEPTION 'The % Command is Disabled', tg_tag;
END; $$
LANGUAGE PLPGSQL;
CREATE FUNCTION
```
Create the event trigger, which will fire before the start of a DDL DROP command.
```
CREATE EVENT TRIGGER trg_abort_drop_command
ON DDL_COMMAND_START
WHEN TAG IN ('DROP TABLE', 'DROP VIEW',
'DROP FUNCTION', 'DROP SEQUENCE',
'DROP MATERIALIZED VIEW', 'DROP TYPE')
EXECUTE PROCEDURE abort_drop_command();
```
Test the trigger by attempting to drop the EMPLOYEES table.
```
DROP TABLE EMPLOYEES;
ERROR: The DROP TABLE Command is Disabled
CONTEXT: PL/pgSQL function abort_drop_command() line 3 at RAISE
```
## Summary
| Trigger | Oracle | PostgreSQL |
| --- | --- | --- |
| Before update trigger, row level | CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
/*Trigger body*/
END;
/
| CREATE TRIGGER check_update
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE myproc();
|
| Before update trigger, statement level | CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE ON projects
BEGIN
/*Trigger body*/
END;
/
| CREATE TRIGGER check_update
BEFORE UPDATE ON employees
FOR EACH STATEMENT
EXECUTE PROCEDURE myproc();
|
| System / event trigger | CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
| CREATE EVENT TRIGGER trg_drops
ON ddl_command_start
EXECUTE PROCEDURE trg_drops();
|
| Referencing :old and :new values in triggers | Use ":NEW" and ":OLD" in trigger body: CREATE OR REPLACE TRIGGER Upper-NewDeleteOld
BEFORE INSERT OR UPDATE
OF first_name ON employees
FOR EACH ROW
BEGIN
:NEW.first_name :=
UPPER(:NEW.first_name);
:NEW.salary := :OLD.salary;
END;
/
| Use ".NEW" and ".OLD" in trigger procedure body: CREATE OR REPLACE FUNCTION
log_ emp_name_upd()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.last_name <> OLD.last_name
THEN
INSERT INTO employee_audit (employee_
id,last_name,changed_on) VALUES
(OLD.id,OLD.last_name,now());
END IF;
RETURN NEW;
END;
$$
CREATE TRIGGER last_name_change_trg
BEFORE UPDATE ON employees FOR
EACH ROW EXECUTE PROCEDURE log_
last_emp_name_upd();
|
| Database event level trigger | CREATE TRIGGER register_shutdown
ON DATABASE SHUTDOWN
BEGIN
Insert into logging values
('DB was shut down', sysdate);
commit;
END;
/
| N/A |
| Drop a trigger | DROP TRIGGER last_name_change_trg;
| DROP TRIGGER last_name_change_trg on employees;
|
| Modify logic run by a trigger | Can be used with create or replace CREATE OR REPLACE TRIGGER
UpperNewDeleteOld
BEFORE INSERT OR UPDATE OF
first_name ON employees
FOR EACH ROW
BEGIN
<>
END;
/
| Use CREATE OR REPLACE on the called function in the trigger (trigger stay the same) CREATE or replace FUNCTION
UpperNewDeleteOld() RETURNS
trigger AS
$UpperNewDeleteOld$
BEGIN
<>
END;
$UpperNewDeleteOld$
LANGUAGE plpgsql;
|
| Enable a trigger | ALTER TRIGGER UpperNewDeleteOld
ENABLE;
| alter table employees enable trigger Upper-NewDeleteOld;
|
| Disable a trigger | ALTER TRIGGER UpperNewDeleteOld
DISABLE;
| alter table employees disable trigger Upper-NewDeleteOld;
|
For more information, see [CREATE TRIGGER](https://www.postgresql.org/docs/13/sql-createtrigger.html) and [Trigger Functions](https://www.postgresql.org/docs/13/plpgsql-trigger.html) in the *PostgreSQL documentation*.
# Oracle and PostgreSQL tablespaces and data files
With AWS DMS, you can migrate data between different database platforms, including Oracle and PostgreSQL, while maintaining the integrity of tablespaces and data files. Tablespaces in Oracle and PostgreSQL are logical storage units that group related data files, allowing for better management and administration of database objects. Data files are the physical files on disk that store the actual data.
| 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) | N/A | N/A | All supported by PostgreSQL except managing the physical data files. |
## Oracle usage
The storage structure of an Oracle database contains both physical and logical elements.
+ **Tablespaces** — Each Oracle database contains one or more tablespaces, which are logical storage groups used as containers for creating new tables and indexes.
+ **Data Files** — Each tablespace is made up of one or more data files, which are the physical elements of an Oracle database tablespace. Datafiles can be located on the local file system, located in raw partitions, managed by Oracle ASM, or located on a network file system.
### Storage Hierarchy
+ **Database** — Each Oracle database is composed of one or more tablespaces.
+ **Tablespace** — Each Oracle tablespace is composed of one or more datafiles. Tablespaces are logical entities that have no physical manifestation on the file system.
+ **Data files** — Physical files located on a file system. Each Oracle tablespace consists of one or more data files.
+ **Segments** — Each segment represents a single database object that consumes storage such as tables, indexes, and undo segments.
+ **Extent** — Each segment consists of one or more extents. Oracle uses extents to allocate contiguous sets of database blocks on disk.
+ **Block** — The smallest unit of I/O for reads and writes. For blocks storing table data, each block can store one or more table rows.
### Types of Oracle Database Tablespaces
+ **Permanent tablespaces** — Designated to store persistent schema objects for applications.
+ **Undo tablespace** — A special type of system permanent tablespace used by Oracle to manage UNDO data when running the database in automatic undo management mode.
+ **Temporary tablespace** — Contains schema objects valid for the duration of a session. It is also used for sort operations that can’t fit into memory.
**Tablespace privileges**
The following criteria must be met to create a tablespace:
+ The database user must have the CREATE TABLESAPCE system privilege.
+ The database must be in OPEN MODE.
**Examples**
Create a USERS tablespace comprised of a single data file.
```
CREATE TABLESPACE USERS
DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
```
Drop a tablespace.
```
DROP TABLESPACE USERS;
OR
DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;
```
For more information, see [CREATE TABLESPACE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLESPACE.html#GUID-51F07BF5-EFAF-4910-9040-C473B86A8BF9), [file\$1specification](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/file_specification.html#GUID-580FA726-F712-4410-90CF-783A2DA89688), and [DROP TABLESPACE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DROP-TABLESPACE.html#GUID-C91F3E94-4503-48DE-9BCA-42E495E6BE11) in the *Oracle documentation*.
## PostgreSQL usage
The logical storage structure in PostgreSQL shares similar concepts as Oracle, utilizing tablespaces for storing database objects. Tablespaces in PostgreSQL are made from datafiles and are used to store different databases and database object.
+ **Tablespace** — the directory where datafiles are stored.
+ **Data files** — file-system files that are placed inside a tablespace (directory) and are used to store database objects such as tables or indexes. Created automatically by PostgreSQL. Similar to how Oracle-Managed-Files (OMF) behave.
**Note**
Unlike Oracle, a PostgreSQL tablespace doesn’t have user-configured segmentation into multiple and separate data files. When you create the tablespace, PostgreSQL automatically creates the necessary files to store the data.
Each table and index are stored in a separate O/S file, named after the table or index’s filenode number.
### Tablespaces in Amazon Aurora PostgreSQL
After an Amazon Aurora PostgreSQL cluster is created, two system tablespaces are automatically provisioned and can’t be modified or dropped.
+ `pg_global tablespace` is used for the shared system catalogs. Stores objects that are visible to all Cluster databases.
+ `pg_default tablespace` is the default tablespace of the `template1` and `template0` databases. Serves as the default tablespace for other databases, by default, unless a different tablespace was explicitly specified during database creation.
One of the main advantages when using Amazon Aurora PostgreSQL is the absence of complexity for storage management. Therefore, creating tablespaces in Aurora PostgreSQL is simplified and has several advantages over a vanilla PostgreSQL database deployment:
When you create tablespaces, the superuser can specify an OS path (location) that doesn’t currently exist. The directory will be implicitly created.
A user-specified tablespace directory will be created under an embedded Amazon RDS/Aurora path. For example, every path specified in the LOCATION clause when creating a new tablespace will be created under the Amazon RDS path of: `/rdsdbdata/tablespaces/`.
Amazon Aurora PostgreSQL uses a unique self-managed shared storage architecture. The DBA doesn’t need to micro-manage most storage aspects of the database.
**Examples**
Creating a tablespace with Amazon Aurora PostgreSQL and view its associated directory.
```
CREATE TABLESPACE TBS_01 LOCATION '/app_data/tbs_01';
\du
Name Owner Location
pg_default rdsadmin
pg_global rdsadmin
tbs_01 rdsadmin /rdsdbdata/tablespaces/app_data/tbs_01
```
**Note**
The newly specified path was created under the embedded base path for Amazon Aurora: `/rdsdbdata/tablespaces/`.
View current tablespaces and associated directories.
```
select spcname, pg_tablespace_location(oid) from pg_tablespace;
```
Drop the PostgreSQL `TBS_01` tablespace.
```
DROP TABLESPACE TBS_01;
```
Alter a tablespace.
```
ALTER TABLESPACE TBS_01 RENAME TO IDX_TBS_01;
ALTER TABLESPACE TO IDX_TBS_01 OWNER TO USER1;
```
Assign a database with a specific tablespace.
```
CREATE DATABASE DB1 TABLESPACE TBS_01;
SELECT DATNAME, PG_TABLESPACE_LOCATION(DATTABLESPACE) FROM PG_DATABASE
WHERE DATNAME='db1';
datname pg_tablespace_location
db1 /rdsdbdata/tablespaces/app_data/tbs_0
```
Assign a table with a specific tablespace.
```
CREATE TABLE TBL(COL1 NUMERIC, COL2 VARCHAR(10))
TABLESPACE TBS_01;
SELECT SCHEMANAME, TABLENAME, TABLESPACE FROM PG_TABLES
WHERE TABLENAME='tbl';
schemaname tablename tablespace
public tbl tbs_01
```
Assign an index with a specific tablespace.
```
CREATE INDEX IDX_TBL ON TBL(COL1)
TABLESPACE TBS_01;
SELECT SCHEMANAME, TABLENAME, INDEXNAME, TABLESPACE FROM PG_INDEXES
WHERE INDEXNAME='idx_tbl';
schemaname tablename indexname tablespace
public tbl idx_tbl tbs_01
```
Alter a table to use a different tablespace.
```
ALTER TABLE TBL SET TABLESPACE TBS_02;
```
**Tablespace exceptions**
`CREATE TABLESPACE` can’t be run inside a transaction block.
A tablespace can’t be dropped until all objects in all databases using the tablespace have been removed or moved.
**Privileges**
The creation of a tablespace in the PostgreSQL database must be performed by a database superuser.
After you create a tablespace, you can use it from any database, provided that the requesting user has sufficient privileges.
**Tablespace Parameters**
The default\$1tablespace parameter controls the system default location for newly created database objects. By default, this parameter is set to an empty value and any newly created database object will be stored in the default tablespace (pg\$1default).
The default\$1tablespace parameter can be altered by using the cluster parameter group.
To verify and to set the default\$1tablespace variable.
```
SHOW DEFAULT_TABLESPACE; -- No value
default_tablespace
SET DEFAULT_TABLESPACE=TBS_01;
SHOW DEFAULT_TABLESPACE;
default_tablespace
tbs_01
```
## Summary
| Feature | Oracle | Aurora PostgreSQL |
| --- | --- | --- |
| Tablespace | Exists as a logical object and made from one or more user-specified or system-generated data files. | Logical object that is tied to a specific directory on the disk where datafiles will be created. |
| Data file | Can be explicitly created and resized by the user. Oracle-Managed-Files (OMF) allow for automatically created data files. Each data file can contain one or more tables and/or indexes. | Behavior is more akin to Oracle Managed Files (OMF). Created automatically in the directory assigned to the tablespace. Single data file stores information for a specific table or index. Multiple data files can exist for a table or index. Additional files are created: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.tables.tablespaces.html) |
| Creates a new tablespace with system-managed datafiles | CREATE TABLESPACE sales_tbs
DATAFILE SIZE 400M;
| create tablespace sales_tbs LOCATION
'/postgresql/data';
|
| Create a new tablespace with user-managed datafiles | CREATE TABLESPACE sales_tbs
DATAFILE '/oradata/sales01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M;
| N/A |
| Alter the size of a datafile | ALTER DATABASE DATAFILE
'/oradata/-sales01.dbf'
RESIZE 100M;
| N/A |
| Add a datafile to an existing tablespace | ALTER TABLESPACE sales_tbs
ADD DATAFILE '/oradata/sales02.dbf'
SIZE 10M;
| N/A |
| Per-database tablespace | Supported as part of the Oracle 12c Multi-Tenant architecture. Different dedicated tablespaces can be created for different pluggable databases and set as the default tablespace for a PDB: ALTER SESSION SET CONTAINER = 'sales';
CREATE TABLESPACE sales_tbs
DATAFILE '/oradata/sales01.dbf' SIZE 1M
AUTOEXTEND ON NEXT 1M;
ALTER DATABASE sales TABLESPACE
sales_tds;
| Tablespaces are shared across all databases but a default tablespace can be created and configured for the database: create tablespace sales_tbs LOCATION
'/postgresql/data';
CREATE DATABASE sales OWNER
sales_app TABLESPACE sales_tbs;
|
| Metadata tables | Data Dictionary tables are stored in the SYSTEM tablespace. | System Catalog tables are stored in the pg\$1global tablespace. |
| Tablespace data encryption | **Supported** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.tables.tablespaces.html) | **Supported** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.tables.tablespaces.html) For more information, see [Encrypting Amazon RDS resources](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html) in the *Amazon Relational Database Service User Guide*. |
For more information, see [Tablespaces](https://www.postgresql.org/docs/13/static/manage-ag-tablespaces.html), [CREATE TABLESPACE](https://www.postgresql.org/docs/13/static/sql-createtablespace.html), [Database File Layout](https://www.postgresql.org/docs/13/static/storage-file-layout.html), [Free Space Map](https://www.postgresql.org/docs/13/static/storage-fsm.html), [System Catalog Information Functions](https://www.postgresql.org/docs/13/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE), [DROP TABLESPACE](https://www.postgresql.org/docs/13/static/sql-droptablespace.html), and [ALTER TABLESPACE](https://www.postgresql.org/docs/13/static/sql-altertablespace.html) in the *PostgreSQL documentation*.
# Oracle and PostgreSQL user-defined types
With AWS DMS, you can migrate user-defined types (UDTs) from Oracle and PostgreSQL databases to compatible target databases. UDTs extend the database’s built-in data types by providing a way to store complex data structures like objects or custom data types.
| 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) | [User-Defined Types](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.udt) | PostgreSQL doesn’t support `FORALL` statement and `DEFAULT` option. PostgreSQL doesn’t support constructors of the collection type. |
## Oracle usage
Oracle refers to user-defined types (UDTs) as `OBJECT TYPES`. These types are managed using PL/SQL. User-defined types enable the creation of application-dedicated, complex data types that are based on, and extend, the built-in Oracle data types.
The `CREATE TYPE` statement supports creating of the following types:app-name:
+ Objects types
+ Varying array (varray) types
+ Nested table types
+ Incomplete types
+ Additional types such as an SQLJ object type (a Java class mapped to SLQ user defined type)
**Examples**
Create an Oracle Object Type to store an employee phone number.
```
CREATE OR REPLACE TYPE EMP_PHONE_NUM AS OBJECT (
PHONE_NUM VARCHAR2(11));
CREATE TABLE EMPLOYEES (
EMP_ID NUMBER PRIMARY KEY,
EMP_PHONE EMP_PHONE_NUM NOT NULL);
INSERT INTO EMPLOYEES VALUES(1, EMP_PHONE_NUM('111-222-333'));
SELECT a.EMP_ID, a.EMP_PHONE.PHONE_NUM FROM EMPLOYEES a;
EMP_ID EMP_PHONE.P
1 111-222-333
```
Create an Oracle object type as a collection of attributes for the employees table.
```
CREATE OR REPLACE TYPE EMP_ADDRESS AS OBJECT (
STATE VARCHAR2(2),
CITY VARCHAR2(20),
STREET VARCHAR2(20),
ZIP_CODE NUMBER);
CREATE TABLE EMPLOYEES (
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(10) NOT NULL,
EMP_ADDRESS EMP_ADDRESS NOT NULL);
INSERT INTO EMPLOYEES VALUES(1, 'John Smith',
EMP_ADDRESS('AL', 'Gulf Shores', '3033 Joyce Street', '36542'));
SELECT a.EMP_ID, a.EMP_NAME, a.EMP_ADDRESS.STATE,
a.EMP_ADDRESS.CITY, a.EMP_ADDRESS.STREET, a.EMP_ADDRESS.ZIP_CODE
FROM EMPLOYEES a;
EMP_ID EMP_NAME STATE CITY STREET ZIP_CODE
1 John Smith AL Gulf Shores 3033 Joyce Street 36542
```
For more information, see [CREATE TYPE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TYPE.html#GUID-E72E3EE6-DE95-4F58-8941-E2F76D0EAE80) and [CREATE TYPE BODY](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TYPE-BODY.html#GUID-C4F1591A-6F62-4897-9039-2C3F066F1E9D) in the *Oracle documentation*.
## PostgreSQL usage
Similar to Oracle, PostgreSQL enables creation of user-defined types using the `CREATE TYPE` statement. A user-defined type is owned by the user who creates it. If a schema name is specified, the type is created under the specified schema.
PostgreSQL supports the creation of several different user-defined types.
+ **Composite** — Stores a single named attribute that is attached to a data type or multiple attributes as an attribute collection. In PostgreSQL, you can also use the `CREATE TYPE` statement standalone with an association to a table.
+ **Enumerated (enum)** — Stores a static ordered set of values. For example, product categories.
```
CREATE TYPE PRODUCT_CATEGORT AS ENUM ('Hardware', 'Software', 'Document');
```
+ **Range** — Stores a range of values, for example, a range of timestamps used to represent the ranges of time of when a course is scheduled.
```
CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
```
For more information, see [Range Types](https://www.postgresql.org/docs/10/rangetypes.html) in the *PostgreSQL documentation*.
+ **Base** — These types are the system core types (abstract types) and are implemented in a low-level language such as C.
+ **Array** — Support definition of columns as multidimensional arrays. An array column can be created with a built-in type or a user-defined base type, enum type, or composite.
```
CREATE TABLE COURSE_SCHEDULE (
COURSE_ID NUMERIC PRIMARY KEY,
COURSE_NAME VARCHAR(60),
COURSE_SCHEDULES text[]);
```
For more information, see [Arrays](https://www.postgresql.org/docs/13/arrays.html) in the *PostgreSQL documentation*.
## PostgreSQL CREATE TYPE synopsis
```
CREATE TYPE name AS RANGE (
SUBTYPE = subtype
[ , SUBTYPE_OPCLASS = subtype_operator_class ]
[ , COLLATION = collation ]
[ , CANONICAL = canonical_function ]
[ , SUBTYPE_DIFF = subtype_diff_function ]
)
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[ , RECEIVE = receive_function ]
[ , SEND = send_function ]
[ , TYPMOD_IN = type_modifier_input_function ]
[ , TYPMOD_OUT = type_modifier_output_function ]
[ , ANALYZE = analyze_function ]
[ , INTERNALLENGTH = { internallength | VARIABLE } ]
[ , PASSEDBYVALUE ]
[ , ALIGNMENT = alignment ]
[ , STORAGE = storage ]
[ , LIKE = like_type ]
[ , CATEGORY = category ]
[ , PREFERRED = preferred ]
[ , DEFAULT = default ]
[ , ELEMENT = element ]
[ , DELIMITER = delimiter ]
[ , COLLATABLE = collatable ]
)
```
PostgreSQL syntax differences from Oracle `CREATE TYPE` statement.
+ PostgreSQL doesn’t support `CREATE OR REPLACE TYPE`.
+ PostgreSQL doesn’t accept `AS OBJECT`.
**Examples**
Create a user-defined type as a dedicated type for storing an employee phone number.
```
CREATE TYPE EMP_PHONE_NUM AS (
PHONE_NUM VARCHAR(11));
CREATE TABLE EMPLOYEES (
EMP_ID NUMERIC PRIMARY KEY,
EMP_PHONE EMP_PHONE_NUM NOT NULL);
INSERT INTO EMPLOYEES VALUES(1, ROW('111-222-333'));
SELECT a.EMP_ID, (a.EMP_PHONE).PHONE_NUM FROM EMPLOYEES a;
emp_id phone_num
1 111-222-333
(1 row)
```
Create a PostgreSQL object type as a collection of Attributes for the employees table.
```
CREATE OR REPLACE TYPE EMP_ADDRESS AS OBJECT (
STATE VARCHAR(2),
CITY VARCHAR(20),
STREET VARCHAR(20),
ZIP_CODE NUMERIC);
CREATE TABLE EMPLOYEES (
EMP_ID NUMERIC PRIMARY KEY,
EMP_NAME VARCHAR(10) NOT NULL,
EMP_ADDRESS EMP_ADDRESS NOT NULL);
INSERT INTO EMPLOYEES
VALUES(1, 'John Smith',
('AL', 'Gulf Shores', '3033 Joyce Street', '36542'));
SELECT a.EMP_NAME,
(a.EMP_ADDRESS).STATE,
(a.EMP_ADDRESS).CITY,
(a.EMP_ADDRESS).STREET,
(a.EMP_ADDRESS).ZIP_CODE
FROM EMPLOYEES a;
emp_name state city street zip_code
John Smith AL Gulf Shores 3033 Joyce Street 36542
```
For more information, see [CREATE TYPE](https://www.postgresql.org/docs/13/sql-createtype.html) and [Composite Types](https://www.postgresql.org/docs/13/rowtypes.htm) in the *PostgreSQL documentation*.
# Oracle unused columns and PostgreSQL ALTER TABLE statement
With AWS DMS, you can identify and analyze unused columns in Oracle databases and migrate data to PostgreSQL. Oracle unused columns is a feature that scans Oracle database schemas to detect columns that are not being used by applications or queries. You can modify the structure of an existing table in a PostgreSQL database by using the `ALTER TABLE` statement. `ALTER TABLE` lets you add, remove, or modify columns and constraints in a table after it has been created.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | N/A | PostgreSQL doesn’t support unused columns. |
## Oracle usage
Oracle provides a method to mark columns as *unused*. Unused columns aren’t physically dropped, but are treated as if they were dropped. Unused columns can’t be restored. Select statements don’t retrieve data from columns marked as unused and aren’t displayed when running a `DESCRIBE` table command.
The main advantage of setting a column to UNUSED is to reduce possible high database load when dropping a column from a large table. To overcome this issue, a column can be marked as unused and then be physically dropped later.
To set a column as unused, use the `SET UNUSED` clause.
**Examples**
```
ALTER TABLE EMPLOYEES SET UNUSED (COMMISSION_PCT);
ALTER TABLE EMPLOYEES SET UNUSED (JOB_ID, COMMISSION_PCT);
```
Display unused columns.
```
SELECT * FROM USER_UNUSED_COL_TABS;
TABLE_NAME COUNT
EMPLOYEES 3
```
Drop the column permanently (physically drop the column).
```
ALTER TABLE EMPLOYEES DROP UNUSED COLUMNS;
```
For more information, see [CREATE TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL doesn’t support marking table columns as *unused*. However, when running the `ALTER TABLE… DROP COLUMN` command, the drop column statement doesn’t physically remove the column; it only makes it invisible to SQL operations. As such, dropping a column is a fast action, but doesn’t reduce the ondisk size of your table immediately because the space occupied by the dropped column isn’t reclaimed.
The unused space is reclaimed by new DML actions, as they use the space that once was occupied by the dropped column. To force an immediate reclamation of storage space, use the `VACUUM FULL` command. Alternatively, run an `ALTER TABLE` statement to force a rewrite.
**Examples**
PostgreSQL `drop column` statement.
```
ALTER TABLE EMPLOYEES DROP COLUMN COMMISSION_PCT;
```
Verify the operation.
```
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'emps1' AND COLUMN_NAME=LOWER('COMMISSION_PCT');
table_name column_name
(0 rows)
```
Use the `VACUUM FULL` command to reclaim unused space from storage.
```
VACUUM FULL EMPLOYEES;
```
Run the `VACUUM FULL` statement with the `VERBOSE` option to display an activity report of the vacuum process that includes the tables vacuumed and the time taken to perform the vacuum operation.
```
VACUUM FULL VERBOSE EMPLOYEES;
```
For more information, see [ALTER TABLE](https://www.postgresql.org/docs/10/sql-altertable.html) and [VACUUM](https://www.postgresql.org/docs/13/sql-vacuum.html) in the *PostgreSQL documentation*.
# Oracle virtual columns and PostgreSQL views and functions
With AWS DMS, you can replicate data from an Oracle database to a PostgreSQL database while creating PostgreSQL views and functions that mimic Oracle’s virtual columns. Oracle virtual columns let you create columns that derive values from other columns or expressions. PostgreSQL lacks a direct equivalent, but you can use views and functions to achieve similar functionality.
| 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) | ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png) | N/A | N/A |
## Oracle usage
Oracle virtual columns appear as normal columns, but their values are calculated instead of being stored in the database. Virtual columns can’t be created based on other virtual columns and can only reference columns from the same table. When creating a virtual column, you can either explicitly specify the data type or let the database select the data type based on the expression.
You can use virtual columns with constraints, indexes, table partitioning, and foreign keys.
Functions in expressions must be deterministic at the time of table creation.
Virtual columns can’t be manipulated by DML operations.
You can use virtual columns in a `WHERE` clause and as part of DML commands.
When you create an index on a virtual column, Oracle creates a function-based index.
Virtual columns don’t support index-organized tables, external, objects, clusters, or temporary tables.
The output of a virtual column expression must be a scalar value.
The virtual column keywords `GENERATED ALWAYS AS` and `VIRTUAL` aren’t mandatory and are provided for clarity only.
```
COLUMN_NAME [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
```
The keyword `AS` after the column name indicates the column is created as a virtual column.
A virtual column doesn’t need to be specified in an `INSERT` statement.
**Examples**
Create a table that includes two virtual columns.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
USER_NAME VARCHAR2(25),
EMAIL AS (LOWER(USER_NAME) || '@aws.com'),
HIRE_DATE DATE,
BASE_SALARY NUMBER,
SALES_COUNT NUMBER,
FINAL_SALARY NUMBER GENERATED ALWAYS AS
(CASE WHEN SALES_COUNT >= 10 THEN BASE_SALARY +
(BASE_SALARY * (SALES_COUNT * 0.05))
END)
VIRTUAL);
```
Insert a new record into the table without specifying values for the virtual column.
```
INSERT INTO EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
USER_NAME, HIRE_DATE,BASE_SALARY, SALES_COUNT)
VALUES(1, 'John', 'Smith', 'jsmith',
'17-JUN-2003', 5000, 21);
```
Select the email Virtual Column from the table.
```
SELECT email FROM EMPLOYEES;
EMAIL FINAL_SALARY
jsmith@aws.com 10250
```
For more information, see [CREATE TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL doesn’t provide a feature that is directly equivalent to a Virtual Column in Oracle before version 12. However, there are workarounds to emulate similar functionality.
Starting with PostgreSQL 12, support for generated columns have been added. Generated columns can be either calculated from other columns values on the fly or calculated and stored. Generated columns are similar to Oracle virtual columns.
Alternatives for virtual columns for PostgreSQL before version 12:app-name:
+ **Views** — Create a view using the function for the virtual column as part of the view syntax.
+ **Function as a column** — Create a function that receives column values from table records (as parameters) and returns a modified value according to a specific expression. The function serves as a Virtual Column equivalent. You can create a PostgreSQL Expression Index (equivalent to Oracle function-based index) that is based on the function.
**Examples**
The email address for a user is calculated based on the USER\$1NAME column that is a physical property of the table.
Create a table that includes a `USER_NAME` column but doesn’t include an email address column.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
USER_NAME VARCHAR(25));
```
Create a PL/pgSQL function which receives the `USER_NAME` value and returns the full email address.
```
CREATE OR REPLACE FUNCTION USER_EMAIL(EMPLOYEES)
RETURNS text AS $$
SELECT (LOWER($1.USER_NAME) || '@aws.com')
$$ STABLE LANGUAGE SQL;
```
Insert data to the table, including a value for `USER_NAME`. During insert, no reference to the `USER_EMAIL` function is made.
```
INSERT INTO EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, USER_NAME)
VALUES(1, 'John', 'Smith', 'jsmith'),
(2, 'Steven', 'King', 'sking');
```
Use the `USER_EMAIL` function as part of a `SELECT` statement.
```
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
USER_NAME,
USER_EMAIL(EMPLOYEES)
FROM EMPLOYEES;
employee_id first_name last_name user_name user_email
1 John Smith jsmith jsmith@aws.com
2 Steven King sking sking@aws.com
```
Create a view that incorporates the `USER_EMAIL` function.
```
CREATE VIEW employees_function AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
USER_NAME,
USER_EMAIL(EMPLOYEES)
FROM EMPLOYEES;
```
Create an expression-based index on the `USER_EMAIL` column for improved performance.
```
CREATE INDEX IDX_USER_EMAIL ON EMPLOYEES(USER_EMAIL(EMPLOYEES));
```
Verify the expression-based index with `EXPLAIN`.
```
SET enable_seqscan = OFF;
EXPLAIN
SELECT * FROM EMPLOYEES
WHERE USER_EMAIL(EMPLOYEES) = 'jsmith@aws.com';
QUERY PLAN
Index Scan using idx_user_email on employees (cost=0.13..8.14 rows=1 width=294)
Index Cond: ((lower((user_name)::text) || '@aws.com'::text) = 'jsmith@aws.com'::text)
```
### DML support
Using triggers, you can populate column values automatically as virtual columns. For this approach, create two PostgreSQL objects:
+ Create a function containing the data modification logic based on table column data.
+ Create a trigger to use the function and run it as part of the DML.
**Examples**
The following code examples show how to automatically populate the `FULL_NAME` column with the values using data from the `FIRST_NAME` and `LAST_NAME` columns.
Create the table.
```
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMERIC PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
FULL_NAME VARCHAR(25));
```
Create a function to concatenate the `FIRST_NAME` and `LAST_NAME` columns.
```
CREATE OR REPLACE FUNCTION FUNC_USER_FULL_NAME ()
RETURNS trigger as '
BEGIN
NEW.FULL_NAME = NEW.FIRST_NAME || '' '' || NEW.LAST_NAME;
RETURN NEW;
END;
' LANGUAGE plpgsql;
```
Create a trigger that uses the function created in the previous step. The function will run before an insert.
```
CREATE TRIGGER TRG_USER_FULL_NAME BEFORE INSERT OR UPDATE
ON EMPLOYEES FOR EACH ROW
EXECUTE PROCEDURE FUNC_USER_FULL_NAME();
```
Verify the functionality of the trigger.
```
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'John', 'Smith'),(2, 'Steven', 'King');
SELECT * FROM EMPLOYEES;
employee_id first_name last_name full_name
1 John Smith John Smith
2 Steven King Steven King
```
Create an index based on the virtual `FULL_NAME` column.
```
CREATE INDEX IDX_USER_FULL_NAME ON EMPLOYEES(FULL_NAME);
```
Verify the expression-based index with `EXPLAIN`.
```
SET enable_seqscan = OFF;
EXPLAIN
SELECT * FROM EMPLOYEES
WHERE FULL_NAME = 'John Smith';
QUERY PLAN
Index Scan using idx_user_full_name on employees (cost=0.13..8.14 rows=1 width=226)
Index Cond: ((full_name)::text = 'John Smith'::text)
```
For more information, see [CREATE TRIGGER](https://www.postgresql.org/docs/13/sql-createtrigger.html) in the *PostgreSQL documentation*.
# Overall Oracle and PostgreSQL indexes summary
With AWS DMS, you can assess the indexing strategies of your Oracle and PostgreSQL databases before migrating them to a new environment. Overall Oracle and PostgreSQL indexes summary provides a comprehensive analysis of the indexes in your source databases, including their types, usage statistics, and potential redundancies.
## Usage
PostgreSQL supports multiple types of Indexes using different indexing algorithms that can provide performance benefits for different types of queries. The built-in PostgreSQL Index types include:
+ **B-Tree** — Default indexes that you can use for equality and range for the majority of queries. These indexes can operate against all datatypes. You can use B-Tree indexes to retrieve NULL values. B-Tree index values are sorted in ascending order by default.
+ **Hash** — Hash Indexes are practical for equality operators. These types of indexes are rarely used because they aren’t transaction-safe. They need to be rebuilt manually in case of failures.
+ **GIN** (Generalized Inverted Indexes) — GIN indexes are useful when an index needs to map a large amount of values to one row, while B-Tree indexes are optimized for cases when a row has a single key value. GIN indexes work well for indexing fulltext search and for indexing array values.
+ **GiST** (Generalized Search Tree) — GiST indexes aren’t viewed as a single type of index but rather as an index infrastructure; a base to create different indexing strategies. GiST indexes enable building general B-Tree structures that you can use for operations more complex than equality and range comparisons. They are mainly used to create indexes for geometric data types and they support full-text search indexing.
+ **BRIN** (Block Range Indexes) — BRIN Indexes store summary data for values stored in sequential physical table block ranges. A BRIN index contains only the minimum and maximum values contained in a group of database pages. Its main advantage is that it can rule out the presence of certain records and therefore reduce query run time.
Additional PostgreSQL indexes (such as SP-GiST) exist but are currently not supported because they require a loadable extension not currently available in Amazon Aurora PostgreSQL.
Starting with PostgreSQL 12 it is now possible to monitor progress of `CREATE INDEX` and `REINDEX` operartions by querying system view `pg_stat_progress_create_index`.
## CREATE INDEX synopsis
```
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ]
ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC
] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
```
By default, the `CREATE INDEX` statement creates a B-Tree index.
**Examples**
Oracle `CREATE/DROP` Index.
```
CREATE UNIQUE INDEX IDX_EMP_ID ON EMPLOYEES (EMPLOYEE_ID DESC);
DROP INDEX IDX_EMP_ID;
```
PostgreSQL `CREATE/DROP` Index.
```
demo=> CREATE UNIQUE INDEX IDX_EMP_ID ON EMPLOYEES (EMPLOYEE_ID DESC);
demo=> DROP INDEX IDX_EMP_ID;
```
Oracle `ALTER INDEX … RENAME`.
```
ALTER INDEX IDX_EMP_ID RENAME TO IDX_EMP_ID_OLD;
```
PostgreSQL `ALTER INDEX … RENAME`.
```
demo=> ALTER INDEX IDX_EMP_ID RENAME TO IDX_EMP_ID_OLD;
```
Oracle `ALTER INDEX … TABLESPACE`.
```
ALTER INDEX IDX_EMP_ID REBUILD TABLESPACE USER_IDX;
```
PostgreSQL `ALTER INDEX … TABLESPACE`.
```
demo=> CREATE TABLESPACE PGIDX LOCATION '/data/indexes';
demo=> ALTER INDEX IDX_EMP_ID SET TABLESPACE PGIDX;
```
Oracle `REBUILD INDEX`.
```
ALTER INDEX IDX_EMP_ID REBUILD;
```
PostgreSQL `REINDEX (REBUILD) INDEX`.
```
demo=> REINDEX INDEX IDX_EMP_ID;
```
Oracle `REBUILD INDEX ONLINE`.
```
ALTER INDEX IDX_EMP_ID REBUILD ONLINE;
```
PostgreSQL `REINDEX (REBUILD) INDEX ONLINE`.
```
demo=> CREATE INDEX CONCURRENTLY IDX_EMP_ID1 ON EMPLOYEES(EMPLOYEE_ID);
demo=> DROP INDEX CONCURRENTLY IDX_EMP_ID;
```
For more information, see [Building Indexes Concurrently](https://www.postgresql.org/docs/13/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY), [ALTER INDEX](https://www.postgresql.org/docs/13/sql-alterindex.html), and [REINDEX](https://www.postgresql.org/docs/13/sql-reindex.html) in the *PostgreSQL documentation*.
## Summary
| Oracle indexes types and features | PostgreSQL compatibility | PostgreSQL equivalent |
| --- | --- | --- |
| B-Tree Index | Supported | B-Tree Index |
| Index-Organized Tables | Supported | PostgreSQL CLUSTER |
| Reverse key indexes | Not supported | N/A |
| Descending indexes | Supported | ASC (default) / DESC |
| B-tree cluster indexes | Not supported | N/A |
| Unique / non-unique indexes | Supported | Syntax is identical |
| Function-based indexes | Supported | PostgreSQL expression indexes |
| Application domain indexes | Not supported | N/A |
| BITMAP index / Bitmap join indexes | Not supported | Consider BRIN index |
| Composite indexes | Supported | Multicolumn indexes |
| Invisible indexes | Not supported | Extension hypopg isn’t currently supported |
| Local and global indexes | Not supported | N/A |
| Partial Indexes for Partitioned Tables (Oracle 12c) | Not supported | N/A |
| CREATE INDEX… / DROP INDEX… | Supported | High percentage of syntax similarity |
| ALTER INDEX… (General Definitions) | Supported | N/A |
| ALTER INDEX… REBUILD | Supported | REINDEX |
| ALTER INDEX… REBUILD ONLINE | Limited support | CONCURRENTLY |
| Index metadata | PG\$1INDEXES (Oracle USER\$1INDEXES) | N/A |
| Index tablespace allocation | Supported | SET TABLESPACE |
| Index Parallel Operations | Not supported | N/A |
| Index compression | No direct equivalent to Oracle index key compression or advanced index compression | N/A |
# Oracle bitmap indexes and PostgreSQL bitmap
With AWS DMS, you can efficiently migrate databases utilizing Oracle bitmap indexes for optimized query performance. Oracle bitmap indexes are data structures that improve the efficiency of data filtering operations on tables with low cardinality columns.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | PostgreSQL doesn’t support BITMAP index. You can use BRIN index in some cases. |
## Oracle usage
Bitmap indexes are task-specific indexes best suited for providing fast data retrieval for OLAP workloads and are generally very fast for read-mostly scenarios. However, bitmap indexes don’t perform well in heavy DML or OLTP workloads.
Unlike B-tree indexes where an index entry points to a specific table row, a bitmap index stores a bitmap for each index key.
Bitmap indexes are ideal for low-cardinality data filtering where the number of distinct values in a column is relatively small.
**Examples**
Create an Oracle bitmap index.
```
CREATE BITMAP INDEX IDX_BITMAP_EMP_GEN ON EMPLOYEES(GENDER);
```
For more information, see [CREATE INDEX](https://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF01209) in the *Oracle documentation*.
## PostgreSQL usage
Amazon Aurora PostgreSQL doesn’t currently provide a directly comparable alternative for Oracle bitmap indexes.
# Oracle and PostgreSQL B-tree indexes
With AWS DMS, you can efficiently migrate your databases between different database platforms while optimizing query performance through B-tree indexes. B-tree indexes are tree data structures that store pointers to rows in a table based on key values, facilitating faster data retrieval for queries involving those keys.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Five star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-5.png) | ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png) | N/A | N/A |
## Oracle usage
B-tree indexes (B stands for balanced), are the most common index type in a relational database and are used for a variety of common query performance enhancing tasks. You can define B-tree indexes as an ordered list of values divided into ranges. They provide superior performance by associating a key with a row or range of rows.
B-tree indexes contain two types of blocks: branch blocks for searching and leaf blocks for storing values. The branch blocks also contain the root branch, which points to lower-level index blocks in the B-tree index structure.
B-tree indexes are useful for primary keys and other high-cardinality columns. They provide excellent data access performance for a variety of query patterns such as exact match searches and range searches. B-tree indexes are the default when you create a new index.
**Examples**
Create a B-Tree index.
```
CREATE INDEX IDX_EVENT_ID ON SYSTEM_LOG(EVENT_ID);
```
For more information, see [CREATE INDEX](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE) in the *Oracle documentation*.
## PostgreSQL usage
When you create an index in PostgreSQL, a B-tree index is created by default. This behavior is similar to the behavior in the Oracle Database. PostgreSQL B-tree indexes have the same characteristics as Oracle and these types of indexes can handle equality and range queries on data. The PostgreSQL optimizer considers using B-tree indexes especially when using one or more of the following operators in queries: `>`, `>=`, `<`, `⇐`, `=`.
In addition, you can achieve performance improvement when using `IN`, `BETWEEN`, `IS NULL` or `IS NOT NULL`.
**Examples**
Create a PostgreSQL B-tree Index.
```
CREATE INDEX IDX_EVENT_ID ON SYSTEM_LOG(EVENT_ID);
OR
CREATE INDEX IDX_EVENT_ID1 ON SYSTEM_LOG USING BTREE (EVENT_ID);
```
For more information, see [CREATE INDEX](https://www.postgresql.org/docs/13/sql-createindex.html) in the *PostgreSQL documentation*.
# Oracle composite indexes and PostgreSQL multi-column indexes
With AWS DMS, you can optimize query performance by creating composite indexes or multi-column indexes on your migrated databases. A composite index (Oracle) or multi-column index (PostgreSQL) is a database index created on multiple columns, allowing queries involving those columns to leverage the index for faster data retrieval.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Five star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-5.png) | ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | N/A |
## Oracle usage
An index created on multiple table columns is known as a multi-column, concatenated, or composite index. The main purpose of composite indexes is to improve the performance of data retrieval for `SELECT` statements when filtering on all, or some, of the composite index columns. When using composite indexes, it is beneficial to place the most restrictive columns at the first position of the index to improve query performance. Column placement order is crucial when using composite indexes because the most prevalent columns are accessed first.
**Examples**
Create a composite index on the `HR.EMPLOYEES` table.
```
CREATE INDEX IDX_EMP_COMPI ON
EMPLOYEES (FIRST_NAME, EMAIL, PHONE_NUMBER);
```
Drop a composite index.
```
DROP INDEX IDX_EMP_COMPI;
```
For more information, see [Composite Indexes](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html#GUID-ABE1DE2A-59CC-4ADE-86A5-426B16459464) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL multi-column indexes are similar to Oracle composite indexes. Currently, only B-tree, GiST, GIN, and BRIN support multi-column indexes. You can specify up to 32 columns to create a multi-column index.
PostgreSQL uses the same syntax as Oracle to create multi-column indexes.
**Examples**
Create a multi-column index on the EMPLOYEES table.
```
CREATE INDEX IDX_EMP_COMPI
ON EMPLOYEES (FIRST_NAME, EMAIL, PHONE_NUMBER);
```
Drop a multi-column index.
```
DROP INDEX IDX_EMP_COMPI;
```
For more information, see [Multicolumn Indexes](https://www.postgresql.org/docs/13/indexes-multicolumn.html) in the *PostgreSQL documentation*.
# Oracle function-based indexes and PostgreSQL expression indexes
With AWS DMS, you can create function-based indexes in Oracle databases and expression indexes in PostgreSQL databases to improve query performance. Function-based indexes in Oracle allow indexing on expressions or function results, while expression indexes in PostgreSQL index expressions based on one or more columns.
| 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) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | PostgreSQL doesn’t support functional indexes that aren’t single-column. |
## Oracle usage
Function-based indexes allow functions to be used in the `WHERE` clause of queries on indexed columns. Function-based indexes store the output of a function applied on the values of a table column. The Oracle query optimizer only uses a function-based index when the function is used as part of a query.
Oracle updates the index for each DML to ensure that the value that returns from the function is correct.
**Examples**
Create a function-based index.
```
CREATE TABLE SYSTEM_EVENTS(
EVENT_ID NUMERIC PRIMARY KEY,
EVENT_CODE VARCHAR2(10) NOT NULL,
EVENT_DESCIPTION VARCHAR2(200),
EVENT_TIME TIMESTAMPNOT NULL);
CREATE INDEX EVNT_BY_DAY ON SYSTEM_EVENTS(
EXTRACT(DAY FROM EVENT_TIME));
```
For more information, see [Indexes and Index-Organized Tables](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html#GUID-797E49E6-2DCE-4FD4-8E4A-6E761F1383D1) and [CREATE INDEX](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL supports expression indexes which are similar to function-based indexes in Oracle.
**Examples**
Create an expression index in PostgreSQL.
```
CREATE TABLE system_events(
event_id NUMERIC PRIMARY KEY,
event_code VARCHAR(21) NOT NULL,
event_description VARCHAR(200),
event_time TIMESTAMP NOT NULL);
CREATE INDEX event_by_day ON system_events(EXTRACT(DAY FROM event_time));
```
Insert records to the `system_events` table, gathering table statistics using the `ANALYZE` statement and verifying that the `EVNT_BY_DAY` expression index is being used for data access.
```
INSERT INTO system_events
SELECT ID AS event_id,
'EVNT-A'||ID+9||'-'||ID AS event_code,
CASE WHEN mod(ID,2) = 0 THEN 'Warning' ELSE 'Critical' END AS event_desc,
now() + INTERVAL '1 minute' * ID AS event_time
FROM
(SELECT generate_series(1,1000000) AS ID) A;
INSERT 0 1000000
ANALYZE SYSTEM_EVENTS;
EXPLAIN
SELECT * FROM SYSTEM_EVENTS
WHERE EXTRACT(DAY FROM EVENT_TIME) = '22';
QUERY PLAN
Bitmap Heap Scan on system_events (cost=729.08..10569.58 rows=33633 width=41)
Recheck Cond: (date_part('day'::text, event_time) = '22'::double precision)
-> Bitmap Index Scan on evnt_by_day (cost=0.00..720.67 rows=33633 width=0)
Index Cond: (date_part('day'::text, event_time) = '22'::double precision)
```
### Partial indexes
PostgreSQL also offers partial indexes, which are indexes that use a `WHERE` clause when created. The biggest benefit of using partial indexes is reduction of the overall subset of indexed data allowing users to index relevant table data only. You can use partial indexes to increase efficiency and reduce the size of the index.
**Example**
Create a PostgreSQL partial index.
```
CREATE TABLE SYSTEM_EVENTS(
EVENT_ID NUMERIC PRIMARY KEY,
EVENT_CODE VARCHAR(10) NOT NULL,
EVENT_DESCIPTION VARCHAR(200),
EVENT_TIME DATE NOT NULL);
CREATE INDEX IDX_TIME_CODE ON SYSTEM_EVENTS(EVENT_TIME)
WHERE EVENT_CODE like '01-A%';
```
For more information, see [Building Indexes Concurrently](https://www.postgresql.org/docs/13/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY) in the *PostgreSQL documentation*.
# Oracle and PostgreSQL invisible indexes
You can create invisible indexes in Oracle Database. An invisible index is a type of index that is not used by the optimizer by default but can still be used if explicitly specified in a query.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | PostgreSQL doesn’t support invisible indexes. |
## Oracle usage
In Oracle, the invisible index feature gives database administrators the ability to create indexes, or change existing indexes, that are ignored by the optimizer. They are maintained during DML operations and are kept relevant, but are different from usable indexes.
The most common uses for invisible indexes are:
+ Testing the effect of a dropped index without actually dropping it.
+ Using a specific index for certain operations or modules of an application without affecting the overall application.
+ Adding an index to a set of columns on which an index already exists.
Database administrators can force the optimizer to use invisible indexes by changing the `OPTIMIZER_USE_INVISIBLE_INDEXES` parameter to true. You can use invisible indexes if they are specified as a HINT.
**Examples**
Change an index to an invisible index.
```
ALTER INDEX idx_name INVISIBLE;
```
Change an invisible index to a visible index.
```
ALTER INDEX idx_name VISIBLE;
```
Create an invisible index.
```
CREATE INDEX idx_name ON employees(first_name) INVISIBLE;
```
Query all invisible indexes.
```
SELECT TABLE_OWNER, INDEX_NAME FROM DBA_INDEXES
WHERE VISIBILITY = 'INVISIBLE';
```
For more information, see [Understand When to Use Unusable or Invisible Indexes](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-3A66938F-73C6-4173-844E-3938A0DBBB54) in the *Oracle documentation*.
## PostgreSQL usage
Currently, Aurora PostgreSQL doesn’t provide a directly comparable alternative for Oracle invisible indexes.
# Oracle index-organized table and PostgreSQL cluster table
With AWS DMS, you can migrate data from Oracle index-organized tables and PostgreSQL cluster tables to target databases. An Oracle index-organized table stores data values in a B-tree index structure, providing excellent performance for queries involving primary key ranges. A PostgreSQL cluster table clusters related rows on disk by rewriting the table using an index, improving performance on queries that retrieve data from indexed columns.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | PostgreSQL doesn’t support index-organized tables, a partial workaround is available. |
## Oracle usage
In Oracle, an index-organized table (IOT) are a special type of index/table hybrid that physically controls how data is stored at the table and index level. When creating a common database table, or heap-organized table, the data is stored unsorted (as a heap). However, when creating an Index-organized table, the actual table data is stored in a B-tree index structure sorted by the primary key of each row. Each leaf block in the index structure stores both the primary key and non-key columns.
IOTs provide performance improvements when accessing data using the primary key because table records are sorted (clustered) using the primary key and physically co-located alongside the primary key.
**Examples**
Create an Oracle index-organized table storing ordered data based on the primary key.
```
CREATE TABLE SYSTEM_EVENTS (
EVENT_ID NUMBER,
EVENT_CODE VARCHAR2(10) NOT NULL,
EVENT_DESCIPTION VARCHAR2(200),
EVENT_TIME DATE NOT NULL,
CONSTRAINT PK_EVENT_ID PRIMARY KEY(EVENT_ID))
ORGANIZATION INDEX;
INSERT INTO SYSTEM_EVENTS VALUES(9, 'EVNT-A1-10', 'Critical', '01-JAN-2017');
INSERT INTO SYSTEM_EVENTS VALUES(1, 'EVNT-C1-09', 'Warning', '01-JAN-2017');
INSERT INTO SYSTEM_EVENTS VALUES(7, 'EVNT-E1-14', 'Critical', '01-JAN-2017');
SELECT * FROM SYSTEM_EVENTS;
EVENT_ID EVENT_CODE EVENT_DESCIPTION EVENT_TIM
1 EVNT-C1-09 Warning 01-JAN-17
7 EVNT-E1-14 Critical 01-JAN-17
9 EVNT-A1-10 Critical 01-JAN-17
```
**Note**
The records are sorted in the reverse order from which they were inserted.
For more information, see [Indexes and Index-Organized Tables](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html#GUID-797E49E6-2DCE-4FD4-8E4A-6E761F1383D1) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL doesn’t support IOTs directly, but offers partially similar functionality using the `CLUSTER` feature. The PostgreSQL `CLUSTER` statement specifies table sorting based on an index already associated with the table. When using the PostgreSQL `CLUSTER` command, the data in the table is physically sorted based on the index, possibly using a primary key column.
**Note**
Unlike an Oracle Index-Organized Table which is defined during table creation and persists data sorting (the IOT will always remain sorted), the PostgreSQL `CLUSTER` doesn’t provide persistent sorting; it is a one-time operation. When the table is subsequently updated, the changes aren’t clustered/sorted.
You can use the `CLUSTER` statement to re-cluster the table.
**Examples**
```
CREATE TABLE SYSTEM_EVENTS (
EVENT_ID NUMERIC,
EVENT_CODE VARCHAR(10) NOT NULL,
EVENT_DESCIPTION VARCHAR(200),
EVENT_TIME DATE NOT NULL,
CONSTRAINT PK_EVENT_ID PRIMARY KEY(EVENT_ID));
INSERT INTO SYSTEM_EVENTS VALUES(9, 'EV-A1-10', 'Critical', '01-JAN-2017');
INSERT INTO SYSTEM_EVENTS VALUES(1, 'EV-C1-09', 'Warning', '01-JAN-2017');
INSERT INTO SYSTEM_EVENTS VALUES(7, 'EV-E1-14', 'Critical', '01-JAN-2017');
CLUSTER SYSTEM_EVENTS USING PK_EVENT_ID;
SELECT * FROM SYSTEM_EVENTS;
event_id event_code event_desciption event_time
1 EVNT-C1-09 Warning 2017-01-01
7 EVNT-E1-14 Critical 2017-01-01
9 EVNT-A1-10 Critical 2017-01-01
INSERT INTO SYSTEM_EVENTS VALUES(2, 'EV-E2-02', 'Warning', '01-JAN-2017');
SELECT * FROM SYSTEM_EVENTS;
event_id event_code event_desciption event_time
1 EVNT-C1-09 Warning 2017-01-01
7 EVNT-E1-14 Critical 2017-01-01
9 EVNT-A1-10 Critical 2017-01-01
2 EVNT-E2-02 Warning 2017-01-01
CLUSTER SYSTEM_EVENTS USING PK_EVENT_ID; -- Run CLUSTER again to re-cluster
SELECT * FROM SYSTEM_EVENTS;
event_id event_code event_desciption event_time
1 EVNT-C1-09 Warning 2017-01-01
2 EVNT-E2-02 Warning 2017-01-01
7 EVNT-E1-14 Critical 2017-01-01
9 EVNT-A1-10 Critical 2017-01-01
```
For more information, see [CLUSTER](https://www.postgresql.org/docs/13/sql-cluster.htm) and [Building Indexes Concurrently](https://www.postgresql.org/docs/13/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY) in the *PostgreSQL documentation*.
# Oracle local and global partitioned indexes and PostgreSQL partitioned indexes
With AWS DMS, you can migrate partitioned tables from Oracle and PostgreSQL databases to Amazon Aurora. Oracle local and global partitioned indexes and PostgreSQL partitioned indexes are database objects that improve query performance by dividing large tables into smaller, more manageable pieces called partitions.
| 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) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | N/A |
## Oracle usage
Local and global indexes are used for partitioned tables in Oracle databases. Each index created on a partitioned table can be specified as either local or global.
+ **Local partitioned index** maintains a one-to-one relationship between the index partitions and the table partitions. For each table partition, Oracle creates a separate index partition. This type of index is created using the `LOCAL` clause. Because each index partition is independent, index maintenance operations are easier and can be performed independently. Local partitioned indexes are managed automatically by Oracle during creation or deletion of table partitions.
+ **Global partitioned index** contains keys from multiple table partitions in a single index partition. This type of index is created using the `GLOBAL` clause during index creation. A global index can be partitioned or non-partitioned (default). Certain restrictions exist when creating global partitioned indexes on partitioned tables, specifically for index management and maintenance. For example, dropping a table partition causes the global index to become unusable without an index rebuild.
**Examples**
Create a local index on a partitioned table.
```
CREATE INDEX IDX_SYS_LOGS_LOC ON SYSTEM_LOGS (EVENT_DATE)
LOCAL
(PARTITION EVENT_DATE_1,
PARTITION EVENT_DATE_2,
PARTITION EVENT_DATE_3);
```
Create a global index on a partitioned table.
```
CREATE INDEX IDX_SYS_LOGS_GLOB ON SYSTEM_LOGS (EVENT_DATE)
GLOBAL PARTITION BY RANGE (EVENT_DATE) (
PARTITION EVENT_DATE_1 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')),
PARTITION EVENT_DATE_2 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')),
PARTITION EVENT_DATE_3 VALUES LESS THAN (TO_DATE('01/01/2017','DD/MM/YYYY')),
PARTITION EVENT_DATE_4 VALUES LESS THAN (MAXVALUE);
```
For more information, see [Partitioning Concepts](https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/partition-concepts.html#GUID-EA7EF5CB-DD49-43AF-889A-F83AAC0D7D51) and [Index Partitioning](https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/index-partitioning.html#GUID-569F94D0-E6E5-45BB-9626-5506DE18FF00) in the *Oracle documentation*.
## PostgreSQL usage
The table partitioning mechanism in PostgreSQL is different when compared to Oracle. There is no direct equivalent for Oracle local and global indexes. The implementation of partitioning in PostgreSQL (table inheritance) includes the use of a parent table with child tables used as the table partitions. Also, when using declarative partitions, global index is still not supported while creating a global index will create an index for each partition, there is a parent index referring to all sub indexes but there is no actual global indexes.
Indexes created on the child tables behave similarly to local indexes in the Oracle database, with portable indexes (partitions). Creating an index on the parent table, such as a global index in Oracle, has no effect.
While concurrent indexes on partitioned tables build are currently not supported, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out. In this case, building the partitioned index is a meta-data only operation.
A `CREATE INDEX` command invoked on a partitioned table, will `RECURSE` (default) to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached.
**Examples**
Create the parent table.
```
CREATE TABLE SYSTEM_LOGS
(EVENT_NO NUMERIC NOT NULL,
EVENT_DATE DATE NOT NULL,
EVENT_STR VARCHAR(500),
ERROR_CODE VARCHAR(10));
```
Create child tables (partitions) with a check constraint.
```
CREATE TABLE SYSTEM_LOGS_WARNING (
CHECK (ERROR_CODE IN('err1', 'err2', 'err3')))
INHERITS (SYSTEM_LOGS);
CREATE TABLE SYSTEM_LOGS_CRITICAL (
CHECK (ERROR_CODE IN('err4', 'err5', 'err6')))
INHERITS (SYSTEM_LOGS);
```
Create Indexes on all child tables (partitions).
```
CREATE INDEX IDX_SYSTEM_LOGS_WARNING ON
SYSTEM_LOGS_WARNING(ERROR_CODE);
CREATE INDEX IDX_SYSTEM_LOGS_CRITICAL ON
SYSTEM_LOGS_CRITICAL(ERROR_CODE);
```
PostgreSQL doesn’t have direct equivalents for local and global indexes in Oracle. However, indexes that have been created on the child tables behave similarly to local indexes in Oracle.
For more information, see [Table Partitioning](https://www.postgresql.org/docs/13/ddl-partitioning.html) in the *PostgreSQL documentation*.
# Oracle automatic indexing and self-managed PostgreSQL
With AWS DMS, you can migrate databases to Amazon Aurora PostgreSQL with the self-managed PostgreSQL option or Oracle databases with the automatic indexing feature. Self-managed PostgreSQL provides more control over database configurations and settings, while automatic indexing optimizes indexes for Oracle databases automatically.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | [Indexes](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.indexes) | PostgreSQL doesn’t provide an automatic indexing feature but in self-managed PostgreSQL instances you can use some extensions for automatic index creation. |
## Oracle usage
Oracle 19 introduces automatic indexing feature. This feature automates the index management tasks by automatically creating, rebuilding, and dropping indexes based on the changes in application workload, thus improving database performance.
Important functionality provided by automatic indexing:
+ Automatic indexing process runs in the background at a predefined time interval and analyzes application workload. It identifies the tables/columns that are candidates for new indexes and creates new indexes.
+ The auto indexes as initially created as invisible indexes. These invisible auto indexes are verified against SQL statements and if the performance is improved, then these indexes are converted as visible indexes.
+ Identify and drop any existing under-performing auto indexes or any auto indexes not used for long period.
+ Rebuilds the auto indexes that are marked unusable due to DDL operations.
+ Provides package DBMS\$1AUTO\$1INDEX to configure automatic indexing and for generating reports related to automatic indexing operations.
**Note**
Up to date table statistics are very important for the Auto indexing to function efficiently. Tables without statistics or with stale statistics aren’t considered for auto indexing.
Package `DBMS_AUTO_INDEX` is used to configuring auto indexes and generating reports. Following are some of the configuration options which can be set by using `CONFIGURE` procedure of `DBMS_AUTO_INDEX` package:
+ Enabling and disabling automatic indexing in a database.
+ Specifying schemas and tables that can use auto indexes.
+ Specifying a retention period for unused auto indexes. By default, the unused auto indexes are deleted after 373 days.
+ Specifying a retention period for unused non-auto indexes.
+ Specifying a tablespace and a percentage of tablespace to store auto indexes.
Following are some of the reports related to automatic indexing operations which you can generate using `REPORT_ACTIVITY` and `REPORT_LAST_ACTIVITY` functions of the `DBMS_AUTO_INDEX` package.
+ Report of automatic indexing operations for a specific period.
+ Report of the last automatic indexing operation.
For more information, see [Managing Indexes](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-E4149397-FF37-4367-A12F-675433715904) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL doesn’t provide an automatic indexing feature. However, in self-managed PostgreSQL instances, you can use extensions such as [Dexter](https://github.com/ankane/dexter) or [HypoPG](https://hypopg.readthedocs.io/en/latest/) to generate indexes with limitations. Amazon Aurora PostgreSQL doesn’t support these extensions.
These extensions use the following approach:
+ Identify the queries.
+ Update the table statistics if they haven’t been analyzed recently.
+ Get the initial cost of the queries and create hypothetical indexes on columns that aren’t already indexes.
+ Get costs again and see if any hypothetical indexes were used. Hypothetical indexes that were used and significantly reduced cost are selected to be indexes.
Find the examples and user guides for [Dexter](https://github.com/ankane/dexter) or [HypoPG](https://hypopg.readthedocs.io/en/latest/) in the official documentation.
Another applicable option for Aurora for PostgreSQL would be to run a scheduled set of queries to estimate if additional indexes are needed.
The following queries can help determine that.
Find user-tables without primary keys.
```
SELECT c.table_schema, c.table_name, c.table_type
FROM information_schema.tables c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog') AND c.table_type = 'BASE TABLE'
AND NOT EXISTS(SELECT i.tablename FROM pg_catalog.pg_indexes i
WHERE i.schemaname = c.table_schema
AND i.tablename = c.table_name AND indexdef LIKE '%UNIQUE%')
AND NOT EXISTS (SELECT cu.table_name FROM information_schema.key_column_usage cu
WHERE cu.table_schema = c.table_schema AND
cu.table_name = c.table_name)
ORDER BY c.table_schema, c.table_name;
```
Query all geometry tables that have no index on the geometry column.
```
SELECT c.table_schema, c.table_name, c.column_name
FROM (SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE') As t
INNER JOIN (SELECT * FROM information_schema.columns WHERE udt_name = 'geometry') c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
(i.tablename = c.table_name AND i.schemaname = c.table_schema
AND indexdef LIKE '%' || c.column_name || '%')
WHERE i.tablename IS NULL
ORDER BY c.table_schema, c.table_name;
```
Unused indexes that can probably be dropped.
```
SELECT s.relname, indexrelname, i.indisunique, idx_scan
FROM pg_catalog.pg_stat_user_indexes s, pg_index i
WHERE i.indexrelid = s.indexrelid and idx_scan = 0;
```
All of these should not be implemented in a script to decide if indexes should be created or dropped in a production environment. The Oracle automatic indexes will first assess if a new index is needed and if so, it will create an invisible index and only after ensuring nothing is harmed, then the index will become visible. This process can’t be used in PostgreSQL to avoid any production performance issues as PostgreSQL doesn’t allow for indexes be created have them be invisible.