User-defined data types (UDT) are also referred as Abstract data types (ADT) or Object types. Object types store structured business data in its natural form and allow applications to retrieve it that way. For that reason they work efficiently with applications developed using object-oriented programming techniques.

While both Oracle and PostgreSQL databases support creating custom data types called as user-defined data types, but Oracle database gives provision to create member functions within a user-defined data type which is not supported in PostgreSQL.

In this article, you will learn how to migrate Oracle user-defined data types to PostgreSQL.

Understanding User-defined data types in Oracle

User-defined data types comprise of Oracle built-in data types and other user-defined data types. The CREATE TYPE in Oracle also supports OR REPLACE to re-create the type if it already exists and provides the flexibility to change the definition of an existing type without dropping it.

Equivalent in PostgreSQL

PostgreSQL provides CREATE TYPE and CREATE DOMAIN features to create user-defined data types. During migration, if you encounter a type in Oracle having only one attribute then migrate as DOMAIN in PostgreSQL, else migrate as TYPE.

You can not use OR REPLACE with CREATE TYPE or CREATE DOMAIN in PostgreSQL. The OBJECT keyword is also not required in PostgreSQL, so you can remove this while creating a domain or type.

When you have single attribute TYPE in Oracle, migrate as DOMAIN in PostgreSQL

Oracle:

CREATE OR REPLACE TYPE OBJECT [ schema. ] type_name { IS | AS }
( 
    attribute1 data_type
);

PostgreSQL:

CREATE DOMAIN [ schema. ] type_name AS data_type;

When you have more than one attribute TYPE in Oracle, migrate as DOMAIN in PostgreSQL

Oracle:

CREATE [ OR REPLACE ] TYPE OBJECT [ schema. ] type_name { IS | AS } 
{ attribute1 data_type,
  attribute2 data_type,
  .
  .
  attributeN data_type
};

PostgreSQL:

--PostgreSQL
CREATE TYPE [ schema. ] type_name AS
{ attribute1 data_type,
  attribute2 data_type,
  .
  .
  attributeN data_type
};