PostgreSQL - How to Create User-Defined Type (UDT)

You can create a user-defined type using CREATE DOMAIN and CREATE TYPE statements.

Quick Example:

   -- Define a type using CREATE DOMAIN
   CREATE DOMAIN addr VARCHAR(90) NOT NULL DEFAULT 'N/A';
   -- Define a type using CREATE TYPE
   CREATE TYPE address AS (city VARCHAR(90), street VARCHAR(90));
 
    -- Use them in a table
    CREATE TABLE location (ship_address addr, full_address address);

CREATE DOMAIN and CREATE TYPE Overview

There are differences between CREATE DOMAIN and CREATE TYPE statements:

CREATE DOMAIN CREATE TYPE
Scalar (Single Field) Type
Complex (Composite) Type
Enumeration Type
DEFAULT Value
NULL and NOT NULL Constraint
CHECK Constraint

Create Scalar Type - Alias for Built-in Data Type

CREATE DOMAIN allows you to create an alias for a built-in data type and specify the range, optional DEFAULT, NOT NULL and CHECK constraint:

   CREATE DOMAIN addr VARCHAR(90) NOT NULL DEFAULT 'N/A';
 
   CREATE DOMAIN idx INT CHECK (VALUE > 100 AND VALUE < 999);

For more details and examples, see CREATE DOMAIN.

Create an Enumeration Type (Enum or Set of Values)

You can use both CREATE DOMAIN and CREATE TYPE to create an enumeration type that can only accept a value from the specified list:

   CREATE DOMAIN color VARCHAR(10)
     CHECK (VALUE IN ('red', 'green', 'blue'));
 
   CREATE TYPE color2 AS ENUM ('red', 'green', 'blue');

Note that values are case-sensitive.

When CREATE TYPE is used, the sorting is performed in the order in which the values are listed in CREATE TYPE statement, not by the actual values.

In case of CREATE DOMAIN, the sorting is based on values in columns:

   CREATE TABLE colors
   (
      color color,       -- type created by CREATE DOMAIN
      color2 color2    -- type created by CREATE TYPE
   );
 
   -- Let's insert the same data and test sorting
   INSERT INTO colors VALUES ('red', 'red');
   INSERT INTO colors VALUES ('blue', 'blue');

Sorting is different:

   -- Sort by CREATE DOMAIN type (alphabetical order)
   SELECT * FROM colors ORDER BY color;
   -- Result:
   -- blue   blue
   -- red    red
 
   -- Sort by CREATE TYPE type (by position)
   SELECT * FROM colors ORDER BY color2;
   -- Result:
   -- red    red
   -- blue   blue

Create a Composite Type

CREATE TYPE allows you to create a composite type containing multiple fields:

   CREATE TYPE full_address AS
   (
       city VARCHAR(90),
       street VARCHAR(90)
   );

Note that you cannot specify NOT NULL, DEFAULT and CHECK constraints for type items.

Let's create a table, insert and query data:

   CREATE TABLE shipping
   (
      name VARCHAR(50),
      address full_address
   );
 
   INSERT INTO shipping VALUES ('John', ('Northampton', 'Tower St'));
   -- or
   INSERT INTO shipping VALUES ('Tom', ROW('Bracknell', 'Market St'));

To access a type field use the syntax: (column_name).field:

   -- Select full composite type
   SELECT address FROM shipping;

Result:

address
(Northampton,”Tower St”)
(Bracknell,”Market St”)
   -- Select each field separately
   SELECT (address).city, (address).street FROM shipping;

Result:

city street
Northampton Tower St
Bracknell Market St

Resources