How to create own data types in PostgreSQL

PostgreSQL allows you to create user-defined data types using CREATE DOMAIN and CREATE TYPE statements.

Using CREATE DOMAIN You can create a subtype based on an existing data type and add some constraints to it. PostgreSQL supports 3 constraints add to domains currently: NULL, NOT NULL, CHECK.
Using CREATE TYPE you can create composite types, enumeration types, or RANGE types.

PostgreSQL `CREATE DOMAIN` syntax

PostgreSQL CREATE DOMAIN statements are used to create a domain. Domains are data types with optional constraints.

To create a domain based on an existing data type, use the CREATE DOMAIN statement :

CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ { NOT NULL | NULL | CHECK (expression) } ]

Explanation:

name: The name of the domain (subtype) to create.
data_type: The base data type of the domain.
collation: The collation of the domain, it is optional.
DEFAULT expression: Use the default value for columns of this data type.
CHECK (expression): CHECK Constraints on columns that use this data type.

For example, to create a domain not_null_text that is a not null TEXT type and has a empty string as default value, use the following statement:

CREATE DOMAIN not_null_text
    AS TEXT
    DEFAULT '';

The following CREATE TABLE statement uses this not_null_text type:

CREATE TABLE test_domain (
  col1 not_null_text,
  col2 not_null_text
);

PostgreSQL `CREATE TYPE`

PostgreSQL CREATE TYPE statements are used to create composite types, enumeration type, or RANGE type.

Syntax

The following syntax uses the CREATE DOMAIN statement to create a composite type:

CREATE TYPE name AS (
  field_name1 data_type
  [, field_name2 data_type, ...]
);

The following syntax uses the CREATE DOMAIN statement to create an enumeration type:

CREATE TYPE name AS ENUM (
  label_1
  [, label_2, ... ]
);

The following syntax uses the CREATE DOMAIN statement to create a RANGE type:

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [, SUBTYPE_OPCLASS = subtype_operator_class ]
    [, COLLATION = collation ]
    [, CANONICAL = canonical_function ]
    [, SUBTYPE_DIFF = subtype_diff_function ]
    [, MULTIRANGE_TYPE_NAME = multirange_type_name ]
);

Create a composite type Examples

Create a custom type address with the following statement:

CREATE TYPE address as (country TEXT, city TEXT);

The above statement creates a custom type named address that has two fields: country and city, both of which are TEXT types.

The following statement converts a complex JSON object to an SQL row:

SELECT
  *
FROM
  json_to_record(
    '{"name": "Tom", "age": 20, "address": {"country": "CN", "city": "BeiJing"}}'
  ) AS x(name TEXT, age INT, address address);

 name | age |   address
------+-----+--------------
 Tom  |  20 | (CN,BeiJing)
(1 row)

Create Enum type Examples

Use the CREATE TYPE statement to create an enumeration type my_color:

CREATE TYPE my_color AS ENUM (
    'yellow',
    'red',
    'blue',
    'green',
    'white',
    'black'
);

Use the PostgreSQL enum_first() function to return the first enumeration value in my_color:

SELECT enum_first(null::my_color);

 enum_first
------------
 yellow

Use the PostgreSQL enum_last() function to return the last enumeration value in my_color:

SELECT enum_last(null::my_color);

 enum_last
-----------
 black

Conclusion

PostgreSQL allows you to create user-defined data types using CREATE DOMAIN and CREATE TYPE statements.

Using CREATE DOMAIN You can create a subtype based on an existing type and add NULL, NOT NULL, or CHECK constraints to it.

Using CREATE TYPE you can create composite types, enumeration types, or RANGE types.

PostgreSQL CREATE DOMAIN syntax