1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. PLSQL to plpgsql
  5. Migrate User defined types

Migrate User defined types

User defined data types (UDT) are also referred as Abstract data types (ADT) or Composite types.
User-defined data types use Oracle built-in data types and other user-defined data types as the building blocks of object types that model the structure and behavior of data in applications. 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
};

Was this article helpful to you? Yes No

How can we help?