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);

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 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.

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 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:

   -- Select each field separately
   SELECT (address).city, (address).street FROM shipping;

Result:

• PostgreSQL Reference and Articles
• CREATE DOMAIN
• CREATE DOMAIN - PostgreSQL 9.1 Documentation
• CREATE TYPE - PostgreSQL 9.1 Documentation