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
		User-Defined Types	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 and CREATE TYPE BODY 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 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 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 and Composite Types in the PostgreSQL documentation.