Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 90 ยท Data Types

Composite Types, Domains and Custom Types

Define your own PostgreSQL types: composite types, reusable domains with constraints, row types, hstore, inet and cidr network types and bit strings.

PostgreSQL lets you shape the type system to fit your data. You are not limited to integer and text plus a pile of repeated CHECK constraints: you can bundle several fields into one composite type, give a constraint a name and reuse it everywhere with a domain, store IP addresses as real network values, or keep a handful of flags in a bit string.

Most of this lesson is PostgreSQL-only, so the PostgreSQL blocks are read-only. SQLite has no custom types, but it can show you the ideas: comparing several values as one (row values), enforcing the same rule on many columns, and doing network arithmetic with bit operations. Those parts run live, and each says what it stands in for.

Four ways to make your own type

Composite Several fields in one value.
Domain A base type plus a reusable rule.
Network types inet and cidr know about masks.
Bit strings Fixed or variable bits you can AND and OR.
1 / 2

Composite types

A composite type is a list of named, typed fields, like a table without rows. CREATE TYPE ... AS (...) defines one, and you can use it as a column type, a function argument or a return type:

SQL
-- PostgreSQL (read-only)
CREATE TYPE address AS (
  street text,
  city   text,
  zip    text
);

CREATE TABLE customers (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text NOT NULL,
  shipping address,
  billing  address
);

INSERT INTO customers (name, shipping, billing)
VALUES ('Ana', ROW('12 Quay Street', 'Bergen', '5003'), ROW('PO Box 9', 'Oslo', '0150'));

To read a field, wrap the column in parentheses: SELECT (shipping).city FROM customers. Without the parentheses PostgreSQL would read shipping.city as table shipping, column city. To change one field, name it on the left side of SET without parentheses: UPDATE customers SET shipping.city = 'Trondheim' WHERE id = 1.

Composite columns are tempting, but they come with a real cost. Each field is not a column of its own, so you cannot give one field a NOT NULL or a foreign key as simply, statistics for it are limited, and every driver and ORM handles the (a,b,c) text format differently. Use composites for function arguments and return values and for small value objects. For data that you query by field, separate columns or a child table is nearly always better.

Every table also is a composite type. A table named customers automatically defines a type customers, so SELECT c FROM customers AS c returns one composite value per row, and a function can take a row as an argument or return SETOF customers. That is the foundation of the row-type tricks in PL/pgSQL: Cursors, Dynamic SQL and Errors.

Comparing as one value

Composite values compare field by field, left to right, like words in a dictionary: the first field decides, the second breaks ties. That is the behaviour behind (year, month) > (2026, 3) and behind keyset pagination (WHERE (created_at, id) > (...)). SQLite supports the same row-value syntax, so you can try it live:

SQL Row values compare field by field

Press Run (or Ctrl + Enter) to see the result here.

Rows 3 and 4 qualify: (2,7) is greater than (2,3) because the first fields tie and 7 > 3, and (3,1) wins on the first field alone. Row 1 loses even though its y is 9, because 1 < 2 already decides it.

Domains: a type with a rule attached

A domain is an existing type plus constraints, a default and an optional NOT NULL. You define the rule once and every column that uses the domain inherits it:

SQL
-- PostgreSQL (read-only)
CREATE DOMAIN email_address AS text
  CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$' AND VALUE = lower(VALUE));

CREATE DOMAIN money_cents AS bigint
  NOT NULL DEFAULT 0
  CHECK (VALUE >= 0);

CREATE DOMAIN rating AS smallint
  CHECK (VALUE BETWEEN 1 AND 5);

CREATE TABLE accounts (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         email_address NOT NULL,
  balance_cents money_cents,
  rating        rating
);

Inside a domain, VALUE stands for the value being checked. After this, a typo in the rule is fixed in one place with ALTER DOMAIN email_address ADD CONSTRAINT ..., instead of in thirty tables. Domains also give a column a meaningful type name in \d output and in query results, so rating documents itself better than smallint.

Watch the details:

  • A CHECK passes when the expression is NULL. A NULL rating satisfies VALUE BETWEEN 1 AND 5, so add NOT NULL to the domain or the column if missing values are not allowed.
  • Domains are checked when a value is assigned or cast, which includes function arguments and results, not only table columns.
  • Adding a constraint to a domain that is already in use re-checks all existing data. ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT lets you avoid a long lock, the same technique as for table constraints in Constraints in Depth.
  • A domain is not a new type in disguise. rating and smallint are interchangeable in expressions.

SQLite has no domains, so each column repeats its own rule. This live version enforces the same three rules as the domains above; the repetition is exactly what a domain removes. It uses INSERT OR IGNORE, which skips rows that break a constraint:

SQL Repeating the same rules per column (what a domain removes)

Press Run (or Ctrl + Enter) to see the result here.

Two rows survive: Ana, and Ed whose NULL rating slips through the CHECK, which is the NULL behaviour described above. Notice that all of this was repeated in the table definition; with domains the table would shrink to three type names.

hstore: key-value pairs

hstore is an extension (see Extensions) that stores a flat map from text keys to text values: 'color=>red, size=>"M"'::hstore. It offers -> to read a key, ? to test for one, || to merge and a GIN index for lookups. It is flat and text-only, so jsonb has largely replaced it for new work, but you will meet it in older schemas. See pg_trgm, hstore, citext, pgcrypto and uuid-ossp.

Network types: inet, cidr and macaddr

PostgreSQL has real types for network addresses, and they are much better than text:

Type Stores Example
inet a host address, optionally with a netmask 192.168.1.5/24
cidr a network, host bits must be zero 192.168.1.0/24
macaddr, macaddr8 a hardware address 08:00:2b:01:02:03

Both IPv4 and IPv6 work. cidr is strict: cidr '192.168.1.5/24' fails with "invalid cidr value" because bits are set to the right of the mask, while the same text is a valid inet. The useful operators are containment, << (is inside) and <<= (is inside or equal), plus >> and >>= the other way round, along with the functions host(), network(), masklen() and family():

SQL
-- PostgreSQL (read-only)
SELECT inet '10.0.3.77' << cidr '10.0.3.0/24';     -- true
SELECT inet '10.0.4.1'  << cidr '10.0.3.0/24';     -- false
SELECT network(inet '10.0.3.77/24');               -- 10.0.3.0/24
SELECT masklen(inet '10.0.3.77/24');               -- 24

-- allow-list lookup, indexed with GiST
CREATE TABLE allowed_networks (net cidr NOT NULL);
CREATE INDEX ON allowed_networks USING gist (net inet_ops);
SELECT EXISTS (SELECT 1 FROM allowed_networks WHERE net >>= inet '10.0.3.77');

An IPv4 address is just a 32-bit number, and a /24 network is a mask that keeps the top 24 bits. SQLite has bitwise operators (&, |, <<, >>, ~), so you can see what << does under the hood. This computes each address as a number and tests containment with a mask:

SQL Network containment with bit operations

Press Run (or Ctrl + Enter) to see the result here.

10.0.3.77 is the number 167773005; masking off the low 8 bits gives 10.0.3.0, so it belongs to the /24. 10.0.4.1 shares the first octet but not the third, so it is inside the /8 but not the /24.

Bit strings

bit(n) stores exactly n bits and bit varying(n) (or varbit) up to n. Literals look like B'10110'. They are useful for compact flag sets and signatures you compare with bitwise operators: & (and), | (or), # (xor), ~ (not), << and >> (shifts), plus get_bit, set_bit, bit_count and length:

SQL
-- PostgreSQL (read-only)
SELECT B'1010' & B'0110';            -- 0010
SELECT B'1010' | B'0110';            -- 1110
SELECT bit_count(B'101101');         -- 4
SELECT get_bit(B'1010', 0);          -- 1   (bit 0 is the leftmost)
SELECT B'101'::int;                  -- 5

One rule to remember: &, | and # between two bit strings require the same length, or PostgreSQL raises an error. For permissions or feature flags a couple of boolean columns, or a text[] of names, are easier to read and query than a bit mask. Reach for bit strings when you really compare many bits at once, such as fingerprints.

Custom types you will not write often

CREATE TYPE name AS ENUM (...) is covered in Booleans and Enums, and CREATE TYPE name AS RANGE (subtype = ...) defines a new range type over any ordered type. Completely new base types need C code and an extension, which is why almost every real schema stays with composites, domains and the built-in types.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you read the city field of a composite column shipping in PostgreSQL?

    The parentheses tell PostgreSQL that shipping is a column and city is one of its fields. Without them it looks for a table named shipping.
  2. 2. A domain is defined as CHECK (VALUE BETWEEN 1 AND 5). What happens when you store NULL in a column of that domain?

    A CHECK only rejects rows where the condition is false. NULL makes it unknown, which passes. Add NOT NULL to the domain or column to forbid missing values.
  3. 3. Why does cidr '192.168.1.5/24' fail while inet '192.168.1.5/24' works?

    inet holds a host address with an optional mask. cidr holds a network, so 192.168.1.5/24 is invalid and 192.168.1.0/24 is the correct form.
  4. 4. How are two composite values such as (2, 7) and (2, 3) compared?

    Row comparison works like alphabetical order. The first fields tie at 2, so the second decides and (2,7) is greater than (2,3).

Summary

  • A composite type groups named fields; read a field with (column).field. Every table is also a composite type.
  • Row values compare left to right, which is what keyset pagination relies on (live in SQLite as well).
  • A domain adds a reusable CHECK, NOT NULL and default to a base type; a CHECK lets NULL through.
  • inet and cidr store addresses and networks with operators like << and >>=; GiST indexes speed them up.
  • Bit strings (bit, bit varying) support &, |, #, ~ and shifts for compact flag sets.
  • hstore is an older flat key-value extension; jsonb is usually the better choice today.

Next: Generated Columns.