Extend PostgreSQL with Custom Types

Ever tried to model a domain value like a currency amount, a coordinate pair, or an email address in PostgreSQL and found yourself repeating validation logic in every query, or worse, storing it as unconstrained text? That's the pain this lesson solves. By the end, you’ll know how to extend PostgreSQL with custom types so your schema speaks your domain language, enforces rules at the database level, and turns messy strings into first-class citizens. This is step 73 in the PostgreSQL Tutorial, right after you’ve mastered tables, indexes, and functions — and it’s the moment your schema stops feeling like a spreadsheet and starts feeling like a well-designed API.

The problem this lesson solves

Standard PostgreSQL types cover the basics: integers, text, timestamps, and JSON. But the real world doesn’t fit neatly into those buckets. Think about a coordinate pair (lat, lng) — you could store it as two float columns, but then every query that needs both values must remember the order, name, and precision rules. Or an email address — you could store it as TEXT, but nothing stops someone from inserting "not-an-email".

The deeper problem is semantic duplication. Without a custom type, your domain rules live only in application code, spread across multiple services. When a new developer joins your team, they have to guess why status is a VARCHAR(20) and what values are allowed. And if you ever need to change the structure — say, adding a timezone to a timestamp — you’ll be wrangling a dozen migrations that touch every table.

Another pain point is query verbosity. Want to compare two points to see if they’re within 5 km? Without a custom type, your SQL becomes a wall of arithmetic. With a custom type and its associated functions, that logic becomes a clean, reusable operation.

Finally, there’s the issue of data integrity at the source. Application-level validation can be bypassed by a lazy ORM call or a manual SQL script run by an admin. The database is the last line of defense, and custom types let you push domain rules right into that boundary.

Imagine dropping a table with stray text that doesn't follow your format — that’s the failure mode you avoid. In this lesson, you’ll learn how to turn a repeated pattern of columns and constraints into one atomic, self-describing type.

Core concept / mental model

Think of a custom type as a structured value with a personality. PostgreSQL already has composite types like point and interval, but they’re built-in. Creating your own lets you define what a value looks like (its structure), what you can do with it (its functions), and how it behaves in queries (its operators).

Here’s the mental model: a custom type is like a class in object-oriented programming, but for data. The definition is the blueprint (fields and types), and the functions are the methods. When you declare a column of that type, every value in that column must conform to the blueprint — and you get the methods for free.

In PostgreSQL, there are two main flavors of custom types:

• Composite types: a list of fields, each with its own type. Think of it as a mini-row. For example, CREATE TYPE coordinate AS (lat float, lng float).
• Enum types: a fixed set of string values. Think of it as a checkbox. For example, CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy').

And there’s also domain types, which are essentially wrappers around existing types with extra constraints — like CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '@'). Domains are lighter-weight and often simpler for validation-only needs.

The key idea is encapsulation: the type carries its own semantics, and the database enforces them. When you read a column of your custom type, you don’t have to remember that the first field is latitude and the second is longitude — the type name tells you, and the attribute access syntax ((value).field) makes it explicit.

Let’s build an analogy: imagine a library card. A built-in type like varchar is just a blank index card — you can write anything. A custom type is a pre-printed card with fields for name, ID, and expiry date, plus a rule that the expiry must be in the future. You can still write the information, but the library won’t accept a card with a past date. That’s the power.

How it works step by step

Creating a custom type is a three-step process, though you can stop at step 1 if you just need a simple structure.

Step 1: Define the type

Use CREATE TYPE with AS to specify either a list of fields (composite) or a set of enum values. For composite types, you list each field name and its PostgreSQL type. For enums, you list the allowed labels in order.

CREATE TYPE coordinate AS (
    lat double precision,
    lng double precision
);

This gives you a new type that you can use in table definitions:

CREATE TABLE places (
    id serial PRIMARY KEY,
    name text,
    location coordinate
);

Step 2: Use the type in DML

To insert data, you can use the ROW constructor syntax or a string literal in parentheses. To read a single field, use the dot notation with parentheses around the column name.

INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ROW(48.8584, 2.2945));

-- or with a string literal
INSERT INTO places (name, location)
VALUES ('Statue of Liberty', '(40.6892, -74.0445)');

-- reading the latitude
SELECT name, (location).lat FROM places;

Step 3 (optional): Add functions and operators

To make the type truly useful, you can define functions that operate on it. For example, a function to calculate the distance between two coordinates, or an operator like -> for easy access. This is where the type begins to feel like a built-in.

CREATE FUNCTION distance_km(a coordinate, b coordinate)
RETURNS double precision AS $$
    SELECT 6371 * acos(
        sin(radians((a).lat)) * sin(radians((b).lat)) +
        cos(radians((a).lat)) * cos(radians((b).lat)) *
        cos(radians((b).lng) - radians((a).lng))
    );
$$ LANGUAGE sql IMMUTABLE;

Now you can use distance_km in queries and even create an index-friendly expression.

The cause-and-effect sequence is clear: define the blueprint → enforce structure → add behavior → reuse across your schema. Every table that uses the type inherits the same rules, reducing drift and bugs.

Hands-on walkthrough

Let’s build a complete, runnable example. We’ll create a custom type for an email address with a validation domain, then use it in a users table.

Step 1: Create the domain (a lightweight custom type)

CREATE DOMAIN email AS text
    CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$');

Now let’s test it. Insert a valid email, then an invalid one.

CREATE TABLE users (
    id serial PRIMARY KEY,
    name text,
    contact email
);

INSERT INTO users (name, contact) VALUES ('Alice', 'alice@example.com');
-- This will fail:
INSERT INTO users (name, contact) VALUES ('Bob', 'not-an-email');

Expected output: the second insert raises an error like value for domain email violates check constraint.

Step 2: Create a composite type for a full address

CREATE TYPE full_address AS (
    street text,
    city text,
    zip text
);

Now add a column to a table and insert a row using ROW:

CREATE TABLE customers (
    id serial PRIMARY KEY,
    name text,
    address full_address
);

INSERT INTO customers (name, address)
VALUES ('Acme Inc', ROW('123 Main St', 'Springfield', '12345'));

SELECT name, (address).city FROM customers;

Output: Acme Inc | Springfield.

Step 3: Add a function to extract the zip code

CREATE FUNCTION zip_of(a full_address) RETURNS text AS $$
    SELECT (a).zip;
$$ LANGUAGE sql IMMUTABLE;

SELECT name, zip_of(address) FROM customers;

This shows how you can extend the type with convenient behaviors.

Step 4 (bonus): Use an enum for status

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id serial PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('shipped');

-- This will fail: 'unknown' is not in the enum
INSERT INTO orders (status) VALUES ('unknown');

Now you have three working examples you can copy, paste, and test in your own PostgreSQL instance (you can use psql or a GUI like pgAdmin).

Compare options / when to choose what

So which flavor should you use? Here’s a comparison table to guide your decision.

When to choose what

• Choose a domain when you need to constrain a single scalar — like an email or a positive number. It’s the easiest to implement and maintain.
• Choose a composite when you have two or more related values that always appear together — like lat/lng or address parts. It reduces column clutter.
• Choose an enum when a column has a small, stable set of allowed strings — like an order status. But be careful: if the list might grow often, a lookup table with a foreign key is often more flexible.

If you need behavior, you can add functions to any of these. For complex types with serialization requirements, you might also explore custom base types written in C or PL/Python, but that’s advanced and rarely needed for typical applications.

Troubleshooting & edge cases

Error: "Cannot alter type ... because column ... uses it" — This happens when you try to change a composite type that’s already used in a table. Solution: either drop the column first or use ALTER TYPE ... ALTER ATTRIBUTE for simple changes. For enum types, you can add a new value with ALTER TYPE status ADD VALUE 'new_status', but you cannot remove or reorder values.

Error: "malformed record literal" when inserting a composite — Your string literal must match the field order and use parentheses, like '(40.6892, -74.0445)'. If you have text fields with commas, you need to quote them, e.g., '("123 Main St", "Springfield, IL", "12345")'.

Gotcha: Enum value ordering — Enum values are sorted by their creation order, not alphabetically. If you rely on ORDER BY on an enum column, it will follow your definition order, not lexical order. If that’s not what you want, add a numeric sort key.

Gotcha: ORM and custom types — Many ORMs (like SQLAlchemy) don’t natively map composite types to a single Python object. You may need to use a TypeDecorator or fetch fields individually. Domains, however, often map to their base type automatically.

Edge case: Indexing composite types — You can’t directly index a composite column, but you can create an index on an expression like ((location).lat) or on a function result. For example: CREATE INDEX ON places (((location).lat));

Domain with NULLs — By default, domains allow NULL unless you add NOT NULL. If you need a non-null domain, you must add the constraint in the domain definition: CREATE DOMAIN email AS text NOT NULL CHECK (VALUE ~ ...);

What you learned & what's next

You now know how to extend PostgreSQL with custom types — from the pain of repeated validation and messy schema, to the core mental model of composite, enum, and domain types, to the step-by-step process of defining types, using them in DML, and adding functions. You’ve seen hands-on examples, compared the three approaches, and troubleshooted common pitfalls like enum-ordering and ORM mapping.

In concrete terms, you’ve achieved the lesson’s learning objectives: you can explain the core idea behind custom types and you’ve completed a practical exercise that uses them in a real table.

Now that you can shape your schema with domain-aware types, the next step in the PostgreSQL Tutorial is to combine them with custom functions and operators to create a full domain language for your queries. That’s where the real power emerges — when your types not only store data but also behave like built-ins. Keep that momentum going.