How to Use Custom Data Types in PostgreSQL

If you’ve spent any real time working with PostgreSQL, you’ve probably noticed it’s not just another relational database that limits you to integers, text, and dates. One of the things that makes Postgres genuinely different from most other databases on the market is that it lets you build your own data types from scratch. Not just tables. Not just functions. Actual, first-class data types that behave exactly like the built-in ones — they can be indexed, cast, compared, and used in every clause where a normal type would go.

This article walks through everything you need to know about custom data types in PostgreSQL: what they are, why you’d bother creating one, the syntax for building them, real examples you can run today, and the pitfalls that trip up even experienced developers.

What Are Custom Data Types in PostgreSQL?

A custom data type is exactly what it sounds like — a data type that doesn’t ship with PostgreSQL by default but that you define yourself. Postgres has a genuinely extensible type system, which means the built-in types like integer, text, boolean, and timestamp aren’t treated any differently from the ones you create. They all live in the same catalog (pg_type), and they all follow the same rules underneath.

There are actually several distinct ways to create a custom type in PostgreSQL, and it’s worth knowing the difference upfront because people often confuse them:

1. Composite types — structured types made up of multiple fields, similar to a row or a struct.
2. Enumerated types (ENUM) — a type restricted to a fixed, ordered list of values.
3. Range types — types that represent a range of values (dates, numbers, etc.).
4. Domain types — a type that’s really just an existing type with extra constraints attached.
5. Base types — genuinely new types written in a low-level language like C, defining their own binary representation.

This article focuses on the general mechanics of building custom types — mainly composite types and domains, since those are the ones most application developers will actually reach for. (I’ve got dedicated articles on composite types, ENUMs, and range types if you want to go deeper on those specifically.)

Why Bother Creating a Custom Type?

I get asked this a lot: why not just use a jsonb column or a few extra columns instead of inventing a new type? There are a handful of good reasons.

Data integrity at the schema level. When you define a type with specific constraints, the database itself enforces the rules — not your application code. That means every application, every script, and every ad-hoc query touching that column gets the same guarantees.

Cleaner, more expressive schemas. A column of type email_address or phone_number is more self-documenting than a varchar(255) with a comment above it that everyone eventually stops reading.

Reusability. Once you define a type, you can use it across as many tables and functions as you want. Update the constraint logic once, and it applies everywhere the type is used.

Better validation than a plain CHECK constraint. Domains let you attach reusable validation logic to a type, so you’re not repeating the same CHECK (col ~ '...') expression in ten different tables.

Creating a Domain Type

The simplest and most commonly used custom type is a domain. A domain is built on top of an existing type but adds constraints, a default value, or a NOT NULL rule.

Here’s the basic syntax:

CREATE DOMAIN domain_name AS underlying_type
    [ DEFAULT expression ]
    [ CONSTRAINT constraint_name ]
    [ NOT NULL | NULL ]
    [ CHECK (expression) ];

Let’s build something practical — a domain for storing positive-only integers, which is a common real-world need:

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

Now you can use positive_integer exactly like you’d use integer:

CREATE TABLE inventory (
    item_id serial PRIMARY KEY,
    item_name text NOT NULL,
    quantity positive_integer NOT NULL
);

Try inserting a negative number, and Postgres will reject it immediately:

INSERT INTO inventory (item_name, quantity) VALUES ('Widget', -5);
-- ERROR:  value for domain positive_integer violates check constraint

Here’s a more realistic example — an email domain type with basic pattern validation:

CREATE DOMAIN email_address AS text
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    full_name text NOT NULL,
    email email_address NOT NULL UNIQUE
);

Notice that this doesn’t just document intent — it actively prevents garbage data from landing in your table, at the database layer, regardless of which application or script is doing the inserting.

Creating a Composite Type

A composite type lets you group several fields together into a single reusable structure — think of it like defining a lightweight struct.

CREATE TYPE address AS (
    street text,
    city text,
    state text,
    postal_code text
);

You can now use address as a column type:

CREATE TABLE warehouses (
    warehouse_id serial PRIMARY KEY,
    warehouse_name text NOT NULL,
    location address
);

Inserting data into a composite column requires the ROW constructor or a parenthesized literal:

INSERT INTO warehouses (warehouse_name, location)
VALUES ('Main Depot', ROW('123 Industrial Way', 'Austin', 'TX', '73301'));

To pull individual fields back out, use dot notation, but you need to wrap the table reference in parentheses because of how Postgres parses composite field access:

SELECT warehouse_name, (location).city, (location).state
FROM warehouses;

I’ll cover composite types in much greater depth — including nesting, arrays of composites, and functions that return them — in the dedicated article on composite types.

Modifying and Managing Custom Types

Once a type exists, you’ll eventually need to change it. Postgres gives you ALTER TYPE for this, though the amount of flexibility depends on which kind of type you’re altering.

Renaming a type:

ALTER TYPE email_address RENAME TO validated_email;

Adding a constraint to a domain:

ALTER DOMAIN positive_integer ADD CONSTRAINT max_value CHECK (VALUE < 1000000);

Dropping a constraint:

ALTER DOMAIN positive_integer DROP CONSTRAINT max_value;

Adding a field to a composite type:

ALTER TYPE address ADD ATTRIBUTE country text;

One important caveat: if a type is already in use by tables or functions, some alterations (like dropping an attribute from a composite type, or changing a domain’s underlying base type) may be restricted or require you to cascade the change, which can affect dependent objects. Always check dependencies before altering a type that’s already in production use.

To see what depends on a type before you touch it:

SELECT DISTINCT dependent.relname
FROM pg_depend
JOIN pg_type t ON t.oid = pg_depend.refobjid
JOIN pg_class dependent ON dependent.oid = pg_depend.objid
WHERE t.typname = 'address';

Dropping a Custom Type

If a type is no longer needed:

DROP TYPE address;

If other objects depend on it, Postgres will refuse to drop it unless you add CASCADE, which will also drop those dependent objects:

DROP TYPE address CASCADE;

Be very careful with CASCADE in production — it’s easy to underestimate how many functions, views, and tables end up depending on a type once it’s been in use for a while.

Casting Between Custom Types and Other Types

Sometimes you need to convert values between your custom type and a related built-in type. Postgres lets you define explicit casts:

CREATE CAST (text AS email_address) WITH INOUT AS IMPLICIT;

This tells Postgres it can automatically treat a plain text value as an email_address where needed, using the domain’s built-in input/output functions. For domains, this is usually automatic since they inherit the underlying type’s casting behavior. For composite and base types, you’ll often need to write explicit cast functions.

Practical Use Cases for Custom Types

Standardizing measurement units. A weight_kg domain that enforces non-negative values keeps unit confusion out of your schema.

Modeling real-world structured data. Composite types are great for things like coordinates, money with currency, or structured addresses that always travel together.

Enforcing business rules at the database level. A percentage domain restricted to values between 0 and 100 prevents an entire category of bugs before they ever reach your application code.

Making function signatures more expressive. A function that returns a composite type like order_summary is far easier to read and maintain than one returning a bare record or a loosely typed JSON blob.

Here’s a function example combining a composite return type with real logic:

CREATE TYPE order_summary AS (
    order_id integer,
    total_items integer,
    total_amount numeric(10,2)
);

CREATE OR REPLACE FUNCTION get_order_summary(p_order_id integer)
RETURNS order_summary AS $$
DECLARE
    result order_summary;
BEGIN
    SELECT o.order_id, COUNT(oi.item_id), SUM(oi.quantity * oi.unit_price)
    INTO result
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_id = p_order_id
    GROUP BY o.order_id;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Calling it:

SELECT * FROM get_order_summary(1042);

Troubleshooting Common Issues

“Type already exists” errors. Postgres type names must be unique within a schema. If you’re re-running scripts during development, wrap your CREATE TYPE in a check, or use DROP TYPE IF EXISTS beforehand in non-production environments.

Domain constraint violations that are hard to trace. When a CHECK constraint on a domain fails, the error message names the domain but not always the specific row or value clearly. Adding a named constraint (CONSTRAINT valid_email CHECK (...)) makes error messages far more readable.

Composite type field access errors. Forgetting the parentheses around a composite column reference (location.city instead of (location).city) is probably the single most common mistake developers make with composite types — Postgres will throw a parse error because it interprets the dot as schema-qualification syntax instead.

Performance surprises with domains. Domains are cheap — they add negligible overhead since the constraint check happens once per write, not per read. Don’t avoid them for performance reasons; that’s rarely where your bottleneck will actually be.

Migration headaches. Altering a domain’s constraints on a large, already-populated table requires validating every existing row against the new constraint, which can be slow. Consider using NOT VALID with domains in newer Postgres versions, then validating separately during a maintenance window if the table is large.

Best Practices

• Name your types clearly and specifically — us_zip_code is more useful than zip.
• Keep composite types focused; if a “type” starts collecting a dozen unrelated fields, it’s probably actually a table.
• Document constraints with named CONSTRAINT clauses rather than anonymous checks, so errors are self-explanatory.
• Version-control your type definitions the same way you version-control table schemas — as part of your migration files, not as one-off manual changes.
• Test constraint edge cases explicitly (empty strings, boundary numbers, nulls) before relying on a domain in production.
• Avoid overusing base types (the C-language kind) unless you have a very specific performance or storage need — they require compiled extensions and add real operational complexity.

Custom Types and ORMs

If you’re working through an ORM (Object-Relational Mapper) rather than raw SQL, custom types deserve a second look before you assume they’ll be painful. Most modern ORMs — SQLAlchemy for Python, ActiveRecord for Ruby, Ecto for Elixir, and others — have some level of support for mapping domains and composite types back into native language constructs, though the depth of that support varies a lot.

A common, pragmatic pattern is to let the database enforce the constraint through a domain, while the application layer treats the column as its underlying type for reads and writes:

CREATE DOMAIN percentage AS numeric(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);

Your application code can keep working with plain numeric values — it never needs to know a domain exists — while the database silently guarantees no invalid percentage ever gets persisted, regardless of which code path wrote it. This is a genuinely underrated way to get “free” validation that survives even bugs in your application layer, database migrations run outside your ORM, or ad-hoc fixes applied directly in psql.

Composite types are a little more involved from an ORM perspective, since most ORMs expect a flat row shape by default. If you’re using composite types heavily and hitting friction, it’s often simpler to expose a view or a function that flattens the composite fields into plain columns for the parts of your application that read through the ORM, while keeping the composite type itself for internal database logic and PL/pgSQL functions.

Custom Types and Database Migrations

Because custom types live in the schema itself, they need to be part of your migration tooling the same way tables and indexes are. A few practical points worth internalizing:

• Order matters. A type must be created before any table or function that references it. Most migration tools handle dependency ordering automatically for tables via foreign keys, but custom types often need to be created explicitly in an earlier migration step.
• Altering domains on large tables can be slow. Adding a CHECK constraint to a domain that’s already used by millions of existing rows requires Postgres to validate every existing value against the new rule. On a large production table, this can hold a lock for longer than you’d like. Consider using NOT VALID (available for constraints in various forms) plus a separate VALIDATE CONSTRAINT step during a lower-traffic window, when your Postgres version and constraint type support it.
• Backward compatibility. If you rename a type or change its structure, remember every function, view, and table using it needs to be updated in the same coordinated migration — a half-applied type change is a common source of confusing deployment errors.

Comparing Custom Types to Alternatives

It’s worth being explicit about what custom types are competing against, because in practice teams often reach for one of these alternatives instead, sometimes for good reason:

Plain columns with application-level validation. Faster to write initially, but the validation only holds as long as every single write path goes through the validating code. Database-level constraints via domains don’t have that gap.

CHECK constraints directly on table columns. These work fine for a one-off rule on a single table, but they don’t get you reusability — if five different tables need the same “valid email” rule, a domain lets you define it once, while repeated CHECK constraints mean five places to keep in sync if the rule ever changes.

JSONB columns for flexible structured data. For genuinely variable, evolving structures, jsonb is usually the better call — it doesn’t require a schema migration every time the shape changes. Composite types are the better choice specifically when the structure is known, stable, and you want the database to enforce that every value actually has the expected shape.

Separate normalized tables. As mentioned earlier, if you find yourself wanting to query, index, or join on individual fields of a composite type frequently, that’s a real signal the data should be a proper table with foreign key relationships instead of a composite column.

A Worked Example: Building Out a Small Type System

To tie the pieces together, here’s a slightly larger example showing custom types working in combination — a simple order system using both a domain and a composite type together.

CREATE DOMAIN currency_amount AS numeric(12,2)
    CHECK (VALUE >= 0);

CREATE TYPE money_with_currency AS (
    amount currency_amount,
    currency_code char(3)
);

CREATE TABLE invoices (
    invoice_id serial PRIMARY KEY,
    customer_name text NOT NULL,
    total money_with_currency NOT NULL
);

INSERT INTO invoices (customer_name, total)
VALUES ('Acme Corp', ROW(1499.99, 'USD'));

-- Attempting an invalid amount fails at the database layer
INSERT INTO invoices (customer_name, total)
VALUES ('Bad Corp', ROW(-50.00, 'USD'));
-- ERROR:  value for domain currency_amount violates check constraint

A helper function that formats this cleanly for display:

CREATE OR REPLACE FUNCTION format_money(m money_with_currency)
RETURNS text AS $$
BEGIN
    RETURN m.currency_code || ' ' || to_char(m.amount, 'FM999,999,999.00');
END;
$$ LANGUAGE plpgsql;

SELECT customer_name, format_money(total) FROM invoices;

This small example demonstrates the real payoff of combining domains and composite types: invalid amounts are rejected automatically no matter what inserted them, the currency and amount always travel together as a single logical unit, and the formatting logic lives in one reusable function rather than being duplicated across every place that displays a monetary value.

Wrapping Up

Custom data types are one of those PostgreSQL features that quietly separate people who use Postgres like “just another SQL database” from people who actually take advantage of what makes it powerful. Domains give you free, database-enforced validation. Composite types let you model real-world structures cleanly. And once you’re comfortable with both, moving on to ENUMs, ranges, and more specialized types becomes a natural next step rather than a leap.

Start small — pick one recurring validation pattern in your schema (an email column, a currency amount, a status field) and turn it into a proper custom type. You’ll be surprised how much cleaner your schema reads, and how many bugs simply stop happening.