Skip to main content

PostgreSQL Custom Types

PostgreSQL offers impressive flexibility with its support for user-defined custom types. This capability allows you to create data types tailored to your specific application needs, making your database schema more expressive and maintainable.

Introduction​

While PostgreSQL comes with numerous built-in data types (integers, text, dates, etc.), real-world data often requires more specialized representations. Custom types enable you to:

  • Create more intuitive data models
  • Enforce data integrity at the type level
  • Improve code readability and maintainability
  • Optimize database performance for your specific use cases

PostgreSQL supports several varieties of custom types, which we'll explore in detail.

Composite Types​

Composite types are similar to structs in C or records in Pascal. They allow you to create a type that combines multiple fields into a single entity.

Creating Composite Types​

sql
CREATE TYPE address AS (
    street_address VARCHAR(100),
    city VARCHAR(50),
    state CHAR(2),
    postal_code VARCHAR(10)
);

Using Composite Types​

Once defined, you can use the type in table definitions:

sql
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    home_address address,
    work_address address
);

Inserting Data​

sql
-- Using ROW constructor
INSERT INTO customers (name, home_address, work_address)
VALUES (
    'John Doe',
    ROW('123 Home St', 'Hometown', 'CA', '94123'),
    ROW('456 Work Ave', 'Worktown', 'CA', '94456')
);

-- Using composite literal syntax
INSERT INTO customers (name, home_address)
VALUES (
    'Jane Smith',
    ('789 Another St', 'Othertown', 'NY', '10001')
);

Querying Composite Types​

Access individual fields with dot notation:

sql
-- Get all customers in California (based on home address)
SELECT name, home_address.city 
FROM customers 
WHERE home_address.state = 'CA';

Enumerated Types (ENUM)​

ENUMs define a static set of values that a field can contain, similar to enumerated types in programming languages.

Creating ENUM Types​

sql
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Using ENUM Types​

sql
CREATE TABLE person (
    name VARCHAR(100),
    current_mood mood
);

INSERT INTO person VALUES ('John', 'happy');
INSERT INTO person VALUES ('Jane', 'sad');
INSERT INTO person VALUES ('Bob', 'ok');

-- This would cause an error because 'anxious' is not in our ENUM
-- INSERT INTO person VALUES ('Alice', 'anxious');

ENUMs provide type safety and are more storage-efficient than strings.

Domain Types​

Domains are custom types based on existing types with additional constraints.

Creating Domain Types​

sql
-- Create a domain for US phone numbers
CREATE DOMAIN us_phone AS VARCHAR(12)
    CHECK (VALUE ~ '^\d{3}-\d{3}-\d{4}$');
    
-- Create a domain for positive integers
CREATE DOMAIN positive_int AS INTEGER
    CHECK (VALUE > 0);

Using Domain Types​

sql
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    phone us_phone,
    priority positive_int
);

-- Valid insertion
INSERT INTO contacts (name, phone, priority)
VALUES ('John Doe', '555-123-4567', 1);

-- These would fail due to constraint violations
-- INSERT INTO contacts (name, phone, priority) VALUES ('Invalid', '5551234567', 1);
-- INSERT INTO contacts (name, phone, priority) VALUES ('Also Invalid', '555-123-4567', -5);

Domains help enforce data integrity and improve code readability by giving clear names to constrained types.

Range Types​

Range types represent a range of values of a specific data type. PostgreSQL supports both built-in range types and custom range types.

Creating Range Types​

sql
-- Create a range type for product IDs
CREATE TYPE product_id_range AS RANGE (
    subtype = INTEGER
);

Using Range Types​

sql
CREATE TABLE product_groups (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    id_range product_id_range
);

INSERT INTO product_groups (name, id_range)
VALUES ('Basic Products', '[1000,2000)');  -- Range from 1000 up to but not including 2000

-- Check if a value is contained in a range
SELECT name FROM product_groups WHERE id_range @> 1500;

Range types are powerful for representing time periods, numeric ranges, or any other interval data.

Array Types​

While not strictly "custom" types (as arrays can be created from any existing type), arrays deserve mention here as they extend basic types.

sql
-- Use arrays directly in table definitions
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    skills TEXT[],  -- Array of text
    scores INTEGER[]  -- Array of integers
);

INSERT INTO employees (name, skills, scores)
VALUES ('John Doe', ARRAY['SQL', 'Python', 'Java'], ARRAY[85, 92, 78]);

-- Query array elements
SELECT name FROM employees WHERE 'Python' = ANY(skills);

-- Array slicing
SELECT name, skills[1:2] FROM employees;

Creating Complex Custom Types with Object-Oriented Features​

For more advanced use cases, PostgreSQL allows you to create full-featured custom types with methods and operators.

Example: Creating a Point Type​

sql
-- Create a type
CREATE TYPE point_2d AS (
    x FLOAT,
    y FLOAT
);

-- Create a function to calculate distance between two points
CREATE OR REPLACE FUNCTION distance(p1 point_2d, p2 point_2d) 
RETURNS FLOAT AS $$
BEGIN
    RETURN sqrt(power(p1.x - p2.x, 2) + power(p1.y - p2.y, 2));
END;
$$ LANGUAGE plpgsql;

-- Create an operator
CREATE OPERATOR <-> (
    LEFTARG = point_2d,
    RIGHTARG = point_2d,
    PROCEDURE = distance
);

Now we can use our custom type and operator:

sql
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position point_2d
);

INSERT INTO locations (name, position)
VALUES ('Point A', ROW(0, 0)), ('Point B', ROW(3, 4));

-- Find distance using our custom operator
SELECT a.name, b.name, a.position <-> b.position AS distance
FROM locations a, locations b
WHERE a.id != b.id;

Real-World Application: Building a GIS Database​

Let's see how custom types can be used in a real-world Geographic Information System (GIS) application:

sql
-- Define address type
CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    state CHAR(2),
    postal_code VARCHAR(10)
);

-- Define coordinate type
CREATE TYPE geo_point AS (
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6)
);

-- Create a function to calculate distance between coordinates
CREATE OR REPLACE FUNCTION geo_distance(p1 geo_point, p2 geo_point) 
RETURNS FLOAT AS $$
DECLARE
    -- Earth radius in kilometers
    R CONSTANT FLOAT := 6371;
    lat1 FLOAT := radians(p1.latitude);
    lon1 FLOAT := radians(p1.longitude);
    lat2 FLOAT := radians(p2.latitude);
    lon2 FLOAT := radians(p2.longitude);
    dlon FLOAT := lon2 - lon1;
    dlat FLOAT := lat2 - lat1;
    a FLOAT;
BEGIN
    -- Haversine formula
    a := sin(dlat/2)^2 + cos(lat1) * cos(lat2) * sin(dlon/2)^2;
    RETURN R * (2 * asin(sqrt(a)));
END;
$$ LANGUAGE plpgsql;

-- Create tables for our GIS application
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    physical_address address,
    coordinates geo_point
);

-- Insert some test data
INSERT INTO locations (name, physical_address, coordinates)
VALUES (
    'Golden Gate Park',
    ROW('501 Stanyan St', 'San Francisco', 'CA', '94117'),
    ROW(37.769421, -122.486214)
);

INSERT INTO locations (name, physical_address, coordinates)
VALUES (
    'Fisherman''s Wharf',
    ROW('Beach Street & The Embarcadero', 'San Francisco', 'CA', '94133'),
    ROW(37.808300, -122.415278)
);

-- Query to find distance between locations
SELECT 
    a.name AS location1, 
    b.name AS location2,
    geo_distance(a.coordinates, b.coordinates) AS distance_km
FROM 
    locations a,
    locations b
WHERE 
    a.id < b.id;

This example demonstrates how custom types can create a more intuitive and maintainable database schema for geographic applications.

Best Practices for Custom Types​

When working with custom types, keep these guidelines in mind:

  1. Use descriptive names - The type name should clearly indicate its purpose.
  2. Document your types - Comment your type definitions and include example usage.
  3. Consider performance - Complex types may impact query performance.
  4. Validate input data - Use constraints and checks to ensure data integrity.
  5. Plan for extensibility - Consider how your type might need to evolve over time.

Limitations of Custom Types​

While powerful, custom types have some limitations:

  • They can make your database schema more PostgreSQL-specific, potentially complicating migrations to other database systems.
  • Complex custom types with many functions and operators can increase maintenance overhead.
  • Some ORMs and database tools may have limited support for custom types.

Summary​

PostgreSQL custom types provide a powerful way to model complex data directly in your database. By creating types like composite types, enums, domains, and ranges, you can build more expressive, maintainable, and robust database schemas.

Custom types allow your database to speak the language of your domain model, making your code more readable and reducing the impedance mismatch between application code and database structure.

Additional Resources​

Exercises​

  1. Create a custom composite type to represent a product with name, price, and weight attributes.
  2. Create an enumerated type for order status (e.g., 'pending', 'shipped', 'delivered', 'cancelled').
  3. Create a domain for email addresses with appropriate validation.
  4. Design a database schema for a library using custom types where appropriate.
  5. Extend the GIS example with additional functions like finding all locations within a certain distance radius.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)