PostgreSQL: Using ‘CREATE TYPE’ to create custom data types

Introduction

Create Type in PostgreSQL allows for the expansion of the default data type system by introducing user-defined types. By doing this, you can model complex data in a way that aligns more closely with your applications’ needs, providing a means to encapsulate both data structure and associated behaviors.

Understanding PostgreSQL’s ‘CREATE TYPE’ statement can vastly enhance the way you manage and interact with data within your databases. Custom data types offer tailored solutions to specific data representation needs that built-in types cannot always address effectively. In this tutorial, we’ll explore how to design and implement custom data types using the ‘CREATE TYPE’ command with progressive complexity through various code examples.

Basic Usage

To start simply, let’s create a basic custom composite type that combines multiple fields:

CREATE TYPE contact AS (
    name VARCHAR(100),
    phone VARCHAR(15)
);

This type ‘contact’, now allows you to store name and phone number as a single structured entity. You can use it as:

CREATE TABLE customer_contacts (
    contact_id serial PRIMARY KEY,
    info contact
);

Inserting data into the table would look like:

INSERT INTO customer_contacts (info) VALUES 
    (ROW('John Doe', '+123456789'));

Composite Types

Going further with composite types, you can model more complex structures. For example, an address:

CREATE TYPE address_type AS (
    street VARCHAR(100),
    city VARCHAR(50),
    zip_code CHAR(5)
);

By defining this, your columns can reflect more in-depth data points. Using it follows similar principles to the previous example.

Enumerated Types

PostgreSQL also supports enumerated types (enums), which represent one value from a finite set:

CREATE TYPE week_day AS ENUM (
    'Sunday',
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday'
);

Enums are great for checking that a value falls within a set range of options, like days of the week or order status:

CREATE TABLE opening_days (
    store_id INT,
    day week_day
);

Range Types

For dealing with ranges, PostgreSQL offers range types. Here’s an example of creating such a type for dates:

CREATE TYPE reservation AS RANGE (
    subtype = date
);

With this, you can specify that certain data occupies a range of dates without needing two separate columns:

CREATE TABLE bookings (
    booking_id serial PRIMARY KEY,
    period reservation
);

Domain Types

Domain types allow you to add constraints to existing data types, effectively creating refined subtypes:

CREATE DOMAIN positive_integer AS INT CHECK (VALUE > 0);

This is like creating a custom ‘rule’ for data to match certain requirements, enhancing data integrity at the type level.

Custom Functions and Types

Building on the previous examples, let’s incorporate functions into our custom types. Suppose we create a type to hold geometrical points.

CREATE TYPE point AS (
    x FLOAT,
    y FLOAT
);

CREATE FUNCTION point_location(p point) RETURNS text AS $
BEGIN
    RETURN format('X: %s, Y: %s', p.x, p.y);
END;
$ LANGUAGE plpgsql;

Here, the function ‘point_location’ helps you return a string representation of a ‘point’. Such functions extend the usefulness of custom types.

Modifying and Dropping Types

If needed, existing types can be modified or removed with careful operations:

ALTER TYPE contact RENAME ATTRIBUTE phone TO phoneNumber;
DROP TYPE IF EXISTS contact CASCADE;

Note that dropping a type with ‘CASCADE’ might drop other database objects that depend on it, so use with caution.

Advanced Considerations

In advanced cases, you might use custom types alongside other PostgreSQL features like arrays or even other custom types you’ve defined. There’s virtually no limit to how nested or intricate these structures can become, provided they serve a meaningful purpose in your database schema.

Conclusion

In conclusion, PostgreSQL’s ‘CREATE TYPE’ adds a versatile layer of abstraction between your data and its representation in the database. By customizing data types, developers gain greater control and specificity, paving the way for more robust and tailored database designs that comport well with application business logic. Understanding and leveraging this feature is a valuable skill in any PostgreSQL developer’s arsenal.