PostgreSQL: How to Create Custom Data Types – CREATE TYPE

This tutorial will show you how to create and use custom data types in PostgreSQL by using the CREATE TYPE statement, and provide examples.

Creating a custom data type is not something most users will need to do (or need to do frequently, at least), however it’s useful to understand how types and custom types work in PostgreSQL should the need to use them arise, or should you be working on someone elses database that uses them.

Custom data types are used when you need to represent data structures that are not represented by the default data types provided by PostgreSQL. for example, you may wish to define a custom type that directly maps to objects in an application, for example an application for storing a contact’s name and phone number, rather than storing those fields in individual table columns.

PostgreSQL CREATE TYPE Syntax

The syntax for using the CREATE TYPE statement in PostgreSQL is as follows:

CREATE TYPE type_name AS (column_name data_type [, ...])

Note that:

• type_name is the name you wish to give the custom type, and will be used when you define columns of that type
• column name and data type should be one or more comma separated pairs that:
  • Give a name to an attribute of the custom data type
  • Provide the type of that attribute (this can be an existing custom data type itself)
• There are many, many more custom options that you can use when creating custom types, which can be found in the official PostgreSQL documentation

Example: Creating a Custom Data Type in PostgreSQL

In the below example, a custom type named contact is created using the CREATE TYPE syntax:

CREATE TYPE contact AS (
    name TEXT,
    phone_number TEXT
);

The contact type has two attributes – a name and phone number.

Example: Using a Custom Data Type in PostgreSQL

The custom type created in the above example can now be used when creating or modifying tables and columns:

CREATE TABLE businesses (
    id SERIAL PRIMARY KEY,
    business_name TEXT,
    ceo contact,
    janitor contact
);

Above, a table named businesses is created. Rather than having columns for the name and phone number for both the CEO and janitor, the contact custom type is used to hold their details, leading to a tidier overall table structure.

Custom types and their attributes can be queried like any other PostgreSQL data type:

SELECT * FROM businesses WHERE ceo.name = 'Freddy';

Above, the businesses table is searched using a SELECT statement, only returning results where the CEO’s name is ‘Freddy’.

You can also modify or delete custom data types using the ALTER TYPE and DROP TYPE statements.