PostgreSQL - CREATE DOMAIN - Create User-Defined Data Type (UDT)

CREATE DOMAIN statement creates a user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint. A domain is an alias for a built-in data type.

Quick Example:

   -- Define a type
   CREATE DOMAIN addr VARCHAR(90);
 
   -- Use it in a table
   CREATE TABLE location (address addr);

Overview:

Syntax (full...)	CREATE DOMAIN name [AS] datatype [constraints]
DEFAULT Value
NOT NULL and NULL		NULL is the default
CHECK Constraint		VALUE keyword to reference checked column
Composite Type		Only single field
Naming	Columns and domain types can have the same name
Alternative	CREATE TYPE statement that supports complex (composite), enum and scalar types

Version: PostgreSQL 9.1

CREATE DOMAIN statement allows you to create an alias for a built-in data type, and assign range and value constraints:

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

Let's create a sample table using the created domain types:

   CREATE TABLE location
   (
     address addr,
     index idx 
   );

Note that idx domain contains VALUE keyword in the CHECK constraint that will be replaced by index column name when the constraint is checked.

Let's insert data:

   INSERT INTO location VALUES('Place', 200);
   -- 1 row affected
 
   -- Insert default address, and index NULL
   INSERT INTO location (index) VALUES(NULL);
   -- 1 row affected
 
   -- Insert address, and index that is out of range
   INSERT INTO location VALUES('Place', 20);
   -- Error: value for domain idx violates check constraint "idx_check"

Note that CHECK constraint does not prevent from inserting NULL values, it is applied to not-NULL values only.

Table content:

Using domains you can also create an ID type:

   CREATE SEQUENCE id_seq;
 
   CREATE DOMAIN id INT DEFAULT NEXTVAL('id_seq') NOT NULL;

And then use it in tables as an identity (auto-increment) column:

   CREATE TABLE products
   (
      id id, 
      name VARCHAR(70)
   );
 
   INSERT INTO products (name) VALUES ('Cake');
   INSERT INTO products (name) VALUES ('Apple');

Table content:

Note that PostgreSQL SERIAL data type is implemented in a similar way.

You can use CREATE DOMAIN to create an enumeration type that can accept only values from a list:

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

Now let's create a table and insert data:

  CREATE TABLE colors (color color);
 
  INSERT INTO colors VALUES ('red');
  -- 1 row affected
 
  INSERT INTO colors VALUES ('Red');
  -- ERROR: value for domain color violates check constraint "color_check"

Note that values are case-sensitive. To allow case-insensitive check, you can use UPPER function in the CHECK constraint:

   CREATE DOMAIN color VARCHAR(10)
     CHECK (UPPER(VALUE) IN ('RED', 'GREEN', 'BLUE'));

CREATE TYPE AS ENUM

You can also use CREATE TYPE statement to create an enumeration type in PostgreSQL:

  CREATE TYPE color2 AS ENUM ('red', 'green', 'blue');

Note that if 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 colors2
   (
      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 colors2 VALUES ('red', 'red');
   INSERT INTO colors2 VALUES ('blue', 'blue');

Sorting is different:

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

• PostgreSQL Reference and Articles
• User-Defined Types (UDT) in PostgreSQL
• CREATE DOMAIN - PostgreSQL 9.1 Documentation