3. Data Types and Domain Modeling

Every column has a data type, and that choice decides what values the column can hold, how much space they take, which operators and indexes work on them, and which mistakes the database catches for you. This chapter goes through PostgreSQL's type system, from basic numbers and text, through dates and time zones, arrays, ranges, and JSON, to specialized and custom types. Picking the right type is the first and cheapest integrity constraint you have. Picking the wrong one leads to rounding errors, time zone bugs, and migrations on large tables that are painful to run.

<a id="31-core-scalar-types"></a>

3.1 Core Scalar Types

This subchapter covers the single-value types that make up most columns in any schema: integers and identity columns, exact and approximate numbers, text and collations, booleans, binary data, and UUIDs.

Integer and Serial Types

Most columns that count things, identify rows, or store quantities hold whole numbers. Integer types are fixed-size, fast to compare, and compact in indexes, so they are the default choice for keys and counters. The only real decision is how large the range must be, and the classic mistake is choosing one that is too small.

PostgreSQL has three integer types:

Type	Aliases	Storage	Range
smallint	int2	2 bytes	-32,768 to 32,767
integer	int, int4	4 bytes	about -2.1 billion to 2.1 billion
bigint	int8	8 bytes	about -9.2 × 10^18 to 9.2 × 10^18

There are no unsigned integers. If a value must be non-negative, add a CHECK (col >= 0) constraint (2.2).

Going outside the range is an error, not a silent wrap-around as in C or Java:

SELECT 2147483647::integer + 1;
-- ERROR:  integer out of range

SELECT 7 / 2;          -- 3: integer division truncates toward zero
SELECT 7 / 2.0;        -- 3.5000000000000000: one numeric operand makes the result numeric
SELECT -7 % 3;         -- -1: the sign follows the dividend

Auto-generated keys: identity columns and serial. A surrogate key (2.2) needs a source of unique numbers. PostgreSQL uses a sequence, a small database object that hands out increasing numbers and is safe under concurrency. There are two ways to attach a sequence to a column:

• Identity columns (GENERATED ... AS IDENTITY, SQL standard, PostgreSQL 10+). The sequence belongs to the column, and permissions, pg_dump, and CREATE TABLE ... LIKE handle it correctly.
• serial / bigserial (legacy shorthand). It is not a real type: serial expands to integer NOT NULL DEFAULT nextval('t_id_seq') plus a separately created sequence. It still works, but it has rough edges, so use identity columns in new schemas.

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- ALWAYS: manual values are rejected
  quantity    integer NOT NULL CHECK (quantity > 0),
  priority    smallint NOT NULL DEFAULT 0                       -- tiny domain, but see the alignment note below
);

INSERT INTO orders (quantity) VALUES (3) RETURNING id;          -- id = 1
INSERT INTO orders (id, quantity) VALUES (100, 1);
-- ERROR:  cannot insert a non-DEFAULT value into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

-- Data migrations sometimes need explicit ids
INSERT INTO orders (id, quantity) OVERRIDING SYSTEM VALUE VALUES (100, 1);
-- ...after which the sequence must be moved past the highest id, or the next INSERT collides
SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders));

Feature	GENERATED ALWAYS AS IDENTITY	GENERATED BY DEFAULT AS IDENTITY	serial
Standard SQL	Yes	Yes	No (PostgreSQL only)
Manual values allowed	Only with OVERRIDING SYSTEM VALUE	Yes	Yes
Sequence ownership and permissions	Automatic	Automatic	Separate object, granted separately
Change the sequence	ALTER TABLE ... ALTER COLUMN id RESTART WITH 1000	Same	ALTER SEQUENCE ...

Sequences have gaps, by design. nextval() is never rolled back. If a transaction takes value 41 and then aborts, 41 is gone forever. Sessions can also cache values (the CACHE option), and a crash can skip a few dozen numbers. This is what lets thousands of concurrent inserts share a sequence without waiting on each other. Never use sequence values as gap-free invoice numbers. If the law requires consecutive numbers, keep a counter row and lock it with UPDATE ... RETURNING, accepting that this serializes the writers.

 Session A: BEGIN; nextval() -> 41; ... ROLLBACK      (41 is lost)
 Session B: BEGIN; nextval() -> 42; ... COMMIT
 Table:     ..., 40, 42, ...                          (a gap is normal)

Choosing a size. Use bigint for primary keys and foreign keys of any table that could grow. The 4 extra bytes per row are cheap. Running out of integer values in a key column with billions of rows is not: converting the column means rewriting the table and every referencing table, usually during an outage. Use integer for ordinary quantities and counts, and smallint only when the domain is truly small. Because of alignment padding, a smallint placed between two bigint columns often saves nothing; column order matters more than the type (6.1).

Key takeaways

• smallint, integer, and bigint are 2, 4, and 8 bytes. Overflow raises an error. There are no unsigned types.
• Integer division truncates. Cast one operand to numeric when you need a fractional result.
• Prefer bigint GENERATED ALWAYS AS IDENTITY for surrogate keys over serial.
• Sequences are non-transactional and produce gaps. Never rely on them for gap-free numbering.
• After loading explicit ids, reset the sequence with setval().

🧪 Practice

1. (Beginner) Create a table with a GENERATED BY DEFAULT AS IDENTITY key. Insert rows with and without explicit ids, until an insert fails with a duplicate key error. Explain why it failed and fix it with setval().
2. (Intermediate) Show that sequences are not transactional: in one session run BEGIN; INSERT ...; ROLLBACK;, then insert again and observe the gap. Then design a gap-free invoice_numbers scheme using a counter table, and explain what happens when two sessions issue invoices concurrently.
3. (Interview) "Our events.id column is an integer and it is at 1.9 billion. What do we do?" Hint: Consider how to find how close each key is to its limit, the cost of ALTER COLUMN ... TYPE bigint on a large table and its referencing foreign keys, and online alternatives (a new column filled in batches, or logical replication into a new table).

Numeric, Decimal, and Floating Point

Computers can store numbers with fractions in two ways. Floating-point numbers are fast and compact, but they are binary approximations: many decimal fractions, such as 0.1, cannot be represented exactly. Arbitrary-precision decimals store digits in base 10, so 0.10 is exactly 0.10, at the cost of speed and variable size. Choosing between them is about whether a small error is acceptable. In a physics simulation it is. In a bank balance it is not.

An analogy: a floating-point number is like measuring with a ruler. You get a very good approximation, but 1/3 of an inch is never exactly on a mark. A numeric is like counting coins: the answer is exact, it just takes longer.

Type	Storage	Precision	Exact	Use for
numeric(p, s) / decimal(p, s)	Variable (about 2 bytes per 4 digits, plus overhead)	Up to 131,072 digits before the point, 16,383 after	Yes	Money, quantities with fixed decimals, anything compared for equality
real (float4)	4 bytes	About 6 significant digits	No	Rarely: compact sensor data where low precision is acceptable
double precision (float8)	8 bytes	About 15 significant digits	No	Scientific and statistical values, measurements, ML features

numeric(p, s) has a precision p (total significant digits) and a scale s (digits after the decimal point). numeric(10, 2) holds values up to 99,999,999.99. Values with more decimals are rounded to the scale. Values too large for the precision raise an error. A bare numeric with no precision accepts any value without rounding.

SELECT 0.1::double precision + 0.2::double precision;          -- 0.30000000000000004
SELECT 0.1::double precision + 0.2 = 0.3::double precision;    -- false
SELECT 0.1::numeric + 0.2 = 0.3;                               -- true: decimal arithmetic is exact

SELECT 12.345::numeric(10, 2);                                 -- 12.35: rounded to the scale
SELECT 123456789.5::numeric(10, 2);
-- ERROR:  numeric field overflow
-- DETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.

-- Rounding rules differ between the types
SELECT round(2.5::numeric), round(3.5::numeric);               -- 3, 4: half away from zero
SELECT round(2.5::float8),  round(3.5::float8);                -- 2, 4: half to even (banker's rounding)

-- Division of numerics never loses precision silently, but it must stop somewhere
SELECT 1::numeric / 3;                                         -- 0.33333333333333333333

Example: a ledger table

CREATE TABLE ledger_entries (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id  bigint        NOT NULL,
  amount      numeric(14, 2) NOT NULL,        -- exact; up to 999,999,999,999.99
  currency    char(3)       NOT NULL,         -- ISO 4217 code: 'EUR', 'USD'
  fx_rate     numeric(18, 8),                 -- exchange rates need more decimals
  latency_ms  double precision                -- a measurement: approximation is fine
);

-- Sums of numeric stay exact no matter how many rows are added
SELECT account_id, sum(amount) FROM ledger_entries GROUP BY account_id;

Special values. Both numeric and floating-point types accept 'NaN', 'Infinity', and '-Infinity' (infinity for numeric since PostgreSQL 14). Unlike in IEEE 754, PostgreSQL treats NaN as equal to itself and greater than every other value, so that it can be sorted and indexed. Add a CHECK constraint if these values should not appear.

The money type. PostgreSQL has a money type, but its formatting depends on the server's lc_monetary setting, it has a fixed number of decimals, and it does not store a currency. Restoring a dump on a server with a different locale can change the meaning of the data. Use numeric plus a currency column instead. Some systems store amounts as a bigint count of the smallest unit (cents). That is exact and fast, but every currency with a different number of decimals needs care.

Key takeaways

• Floating-point types (real, double precision) are approximate. Never use them for money or for values compared with =.
• numeric(p, s) is exact. It rounds to the scale on input and raises an error when the precision is exceeded.
• round() on numeric rounds half away from zero. On double precision it rounds half to even.
• Avoid the money type. Use numeric with an explicit currency column, or integer minor units.

🧪 Practice

1. (Beginner) Insert the value 0.1 ten times into a double precision column and ten times into a numeric column. Compare sum() of each with 1. Explain the result.
2. (Intermediate) Design a prices table that supports EUR (2 decimals), JPY (0 decimals), and BHD (3 decimals). Add a constraint that rejects an amount with more decimals than its currency allows. (Hint: scale() returns the number of decimal digits of a numeric.)
3. (Interview) "Why do some payment systems store amounts as integers in cents instead of decimals?" Hint: Compare exactness, speed, language interoperability (JSON numbers, JavaScript doubles), and what happens with currencies whose minor unit is not 1/100.

Character Types and Collations

Text is the most common data type and the one with the most hidden complexity. Storing it is easy. The hard questions are comparing and sorting it: is 'Apple' equal to 'apple'? Does 'Zebra' come before 'apple'? Where does 'Ångström' go? The answers depend on language and culture, and PostgreSQL captures them in a collation.

The three character types

Type	Behavior	Recommendation
text	Any length (up to 1 GB)	The default choice
varchar(n) / character varying(n)	Any length up to n characters; longer values raise an error	When a length limit is a real business rule
char(n) / character(n)	Exactly n characters, padded with spaces	Almost never

In PostgreSQL, text and varchar are stored the same way and are equally fast. varchar(n) only adds a length check. Many teams use text everywhere and express limits as CHECK (char_length(col) <= n), because changing a check constraint is easier than changing a type. Long values are compressed and stored out of line automatically by TOAST (6.1), so large text does not slow down scans of other columns.

char(n) is a trap. Values are padded with spaces, and trailing spaces are ignored in comparisons but removed when converting to text:

SELECT 'abc'::char(5) = 'abc  '::char(5);     -- true: trailing spaces are not significant
SELECT octet_length('abc'::char(5));          -- 5: the padding is stored
SELECT 'abc'::char(5) || 'x';                 -- 'abcx': padding removed on conversion to text
SELECT 'abcdef'::varchar(3);                  -- 'abc': an explicit cast truncates silently...
INSERT INTO t (code_varchar3) VALUES ('abcdef');
-- ERROR:  value too long for type character varying(3)  -- ...but an INSERT raises an error

Encodings and characters. A database has one character encoding, almost always UTF8. char_length() (or length()) counts characters. octet_length() counts bytes: 'é' is 1 character and 2 bytes.

Collations: how text compares and sorts. A collation is a set of rules for ordering and equality. It affects ORDER BY, < and >, min() and max(), unique constraints, and B-tree indexes. Collations come from a provider:

Provider	Source	Notes
libc	The operating system's C library	Default on many installs. Sort order can change when the OS's glibc is upgraded
icu	The ICU library	Consistent across platforms, rich options (case-insensitive, numeric ordering)
builtin	PostgreSQL itself (17+)	C.UTF-8 (17) and PG_UNICODE_FAST (18): fast, stable code-point order

-- Sorting differs by collation
SELECT name FROM (VALUES ('apple'), ('Banana'), ('cherry'), ('Ápple')) AS t(name)
ORDER BY name COLLATE "C";              -- byte order: Banana, apple, cherry, Ápple

SELECT name FROM (VALUES ('apple'), ('Banana'), ('cherry'), ('Ápple')) AS t(name)
ORDER BY name COLLATE "en-x-icu";       -- linguistic: apple, Ápple, Banana, cherry

-- A per-column collation
CREATE TABLE products (
  sku   text COLLATE "C",               -- codes: byte order is fastest and stable
  name  text COLLATE "de-x-icu"         -- displayed to German users
);

Case-insensitive comparison. There are three common approaches:

-- 1. Normalize in an expression index and always query with lower()
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower('Alice@Example.com');

-- 2. A nondeterministic ICU collation: equality itself ignores case
CREATE COLLATION case_insensitive (
  provider = icu, locale = 'und-u-ks-level2', deterministic = false   -- level2: ignore case, not accents
);
CREATE TABLE accounts (email text COLLATE case_insensitive UNIQUE);
INSERT INTO accounts VALUES ('alice@example.com');
INSERT INTO accounts VALUES ('ALICE@example.com');   -- ERROR: duplicate key value

-- 3. The citext extension: a case-insensitive text type
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE subscribers (email citext UNIQUE);

Nondeterministic collations have costs: they are slower, and pattern matching with LIKE on them is only supported from PostgreSQL 18.

Collation changes corrupt indexes. A B-tree index stores values in sorted order. If an OS upgrade changes glibc's sort rules, existing indexes are suddenly sorted "wrongly", and lookups can miss rows or unique constraints can let duplicates in. PostgreSQL records collation versions and warns about a mismatch. The fix is REINDEX followed by ALTER COLLATION ... REFRESH VERSION (or ALTER DATABASE ... REFRESH COLLATION VERSION). ICU and the builtin provider make this easier to control (11.4).

Key takeaways

• Use text by default. varchar(n) adds only a length check, and char(n) pads with spaces and should be avoided.
• A collation decides equality and sort order, and it is part of every text index.
• Use COLLATE "C" (or the builtin provider) for codes and identifiers, and linguistic collations for human-readable text.
• For case-insensitive uniqueness, use a lower() expression index, a nondeterministic ICU collation, or citext.
• OS library upgrades can change libc collations. Check collation version warnings and reindex after upgrades.

🧪 Practice

1. (Beginner) Create a table with a char(10), a varchar(10), and a text column. Insert 'hi' into each and compare length(), octet_length(), and the result of concatenating '!'.
2. (Intermediate) Sort the list 'item10', 'item2', 'Item1', 'item1' with COLLATE "C", with an ICU collation, and with a custom ICU collation using 'und-u-kn-true' (numeric ordering). Explain each order.
3. (Interview) "After an OS upgrade on a database server, a unique constraint on usernames allowed a duplicate. How is that possible?" Hint: Think about what a B-tree index assumes about sort order, and where that order comes from.

Boolean Type

Many facts are yes-or-no: is the account active, has the email been verified, is the product in stock. The boolean type stores exactly that, in 1 byte. The interesting part is not the type but the third state: like every SQL type, a boolean column can be NULL, which means "unknown", and that turns two-valued logic into three-valued logic.

CREATE TABLE feature_flags (
  name      text    PRIMARY KEY,
  enabled   boolean NOT NULL DEFAULT false,   -- NOT NULL: a flag is on or off, never unknown
  beta_only boolean NOT NULL DEFAULT false
);

-- Accepted input literals (case-insensitive, surrounding spaces ignored)
SELECT 'true'::boolean, 'yes'::boolean, 'on'::boolean, '1'::boolean, 't'::boolean;    -- all true
SELECT 'false'::boolean, 'no'::boolean, 'off'::boolean, '0'::boolean, 'f'::boolean;   -- all false
-- Output is always t or f in psql

Three-valued logic. With NULL in play, AND, OR, and NOT follow these rules:

 a       b       a AND b   a OR b    NOT a
 true    NULL    NULL      true      false
 false   NULL    false     NULL      true
 NULL    NULL    NULL      NULL      NULL

A WHERE clause keeps only rows where the condition is true. So WHERE NOT enabled does not return rows where enabled is NULL. To treat NULL explicitly, use the IS predicates, which always return true or false:

Predicate	true	false	NULL
x IS TRUE	true	false	false
x IS NOT TRUE	false	true	true
x IS FALSE	false	true	false
x IS UNKNOWN	false	false	true

-- Rows where the flag is not explicitly on, including unknown
SELECT * FROM users WHERE newsletter_opt_in IS NOT TRUE;

-- Boolean aggregates
SELECT bool_and(enabled) AS all_on,     -- true only if every row is true
       bool_or(enabled)  AS any_on,     -- true if at least one row is true
       count(*) FILTER (WHERE enabled) AS on_count
FROM feature_flags;

Indexing booleans. A regular index on a boolean column rarely helps, because it splits the table into only two groups. When one value is rare (for example 1% of orders are is_flagged), a partial index on the rare value is small and effective (6.3):

CREATE INDEX orders_flagged_idx ON orders (created_at) WHERE is_flagged;

Modeling advice. A boolean often hides richer information:

• is_verified boolean loses when verification happened. verified_at timestamptz (nullable) answers both "is it verified" (verified_at IS NOT NULL) and "when".
• Several booleans that cannot be true together (is_draft, is_published, is_archived) are really one state. Use a single status column with an enum or lookup table, so invalid combinations cannot exist.
• Make boolean columns NOT NULL with a default unless "unknown" is a real, distinct answer.

Key takeaways

• boolean is 1 byte and accepts true/false, yes/no, on/off, 1/0, t/f.
• NULL makes logic three-valued. WHERE NOT x skips rows where x is NULL. Use IS TRUE, IS NOT TRUE, and similar predicates to handle it.
• Use partial indexes for rare boolean values instead of plain indexes.
• Consider a nullable timestamp instead of a flag, and a status column instead of several mutually exclusive flags.

🧪 Practice

1. (Beginner) Create a table with a nullable boolean column and insert true, false, and NULL. Count rows returned by WHERE x, WHERE NOT x, WHERE x IS NOT TRUE, and WHERE x IS DISTINCT FROM true.
2. (Intermediate) A posts table has is_draft, is_published, and is_deleted booleans, and some rows have more than one set to true. Write a query that finds the inconsistent rows, then design a replacement schema that makes the inconsistency impossible.
3. (Interview) "Should users.is_admin be a boolean column?" Hint: Think about what happens when you need a second or third role, auditing who granted the privilege and when, and the difference between a flag and a relationship.

Binary Data with bytea

Some data is not text: image thumbnails, file hashes, encryption keys, signatures, serialized protocol buffers. Storing them in a text column fails as soon as a byte sequence is not valid UTF-8, and it would also apply collation rules that make no sense for bytes. The bytea ("byte array") type stores raw bytes of any value, from 0 to about 1 GB.

Input and output formats. Bytes have to be written as text in SQL statements and shown as text in results. The default hex format writes \x followed by two hex digits per byte:

SELECT '\xDEADBEEF'::bytea;               -- \xdeadbeef
SELECT length('\xDEADBEEF'::bytea);       -- 4 (bytes)

-- Converting between text and bytes needs an explicit encoding
SELECT convert_to('héllo', 'UTF8');       -- \x68c3a96c6c6f: 'é' is two bytes in UTF-8
SELECT convert_from('\x68c3a96c6c6f', 'UTF8');   -- héllo

-- encode/decode for transport formats
SELECT encode('hello'::bytea, 'base64');  -- aGVsbG8=
SELECT decode('aGVsbG8=', 'base64');      -- \x68656c6c6f
SELECT encode(sha256('hello'::bytea), 'hex');   -- built-in hash functions return bytea

In application code, do not build these literals by hand. Pass the bytes as a bound parameter, and the driver (psycopg, JDBC, node-postgres) transfers them in binary or hex for you.

Example: content-addressed file metadata

CREATE TABLE attachments (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sha256        bytea  NOT NULL UNIQUE CHECK (length(sha256) = 32),   -- 32 bytes, not 64 hex characters
  content_type  text   NOT NULL,
  size_bytes    bigint NOT NULL,
  thumbnail     bytea,                                               -- small preview, a few KB
  storage_key   text   NOT NULL                                      -- where the full file lives (e.g. an S3 key)
);

Storing a hash as bytea takes half the space of its hex text form, and comparisons are plain byte comparisons.

Files in the database or outside it?

Approach	Pros	Cons
bytea column	Transactional with the rest of the row, included in backups, simple	Whole value is read and written at once; bloats backups and replication; 1 GB limit
Large objects (lo_* functions)	Streaming reads and writes, up to 4 TB	Separate cleanup needed (orphaned objects), awkward API, permissions are separate
Object storage + key in a text column	Cheap, scales to any size, served directly by CDNs	Not transactional: the file and the row can get out of sync

A common rule of thumb: keep small binary values (hashes, keys, thumbnails up to a few hundred KB) in bytea, and put large files in object storage with their metadata and hash in the database.

Key takeaways

• bytea stores arbitrary bytes, up to about 1 GB per value. The default text form is hex: \x....
• Use convert_to / convert_from to switch between text and bytes, and encode / decode for base64 and hex.
• Pass binary data as bound parameters rather than literals.
• Store hashes and keys as bytea. Store large files outside the database and keep only metadata and a reference.

🧪 Practice

1. (Beginner) Compute the SHA-256 of 'PostgreSQL' with sha256(). Show it as hex and as base64, and compare octet_length() of the bytea value and of the hex string.
2. (Intermediate) Write a small Python script with psycopg that inserts a binary file into a bytea column and reads it back, and verify that the bytes are identical by comparing hashes computed in Python and in SQL.
3. (Interview) "Users upload profile pictures. Would you store them in PostgreSQL?" Hint: Consider size, backup and replication volume, consistency between the file and the row, how images are served, and what you would store in the database even if the image lives elsewhere.

UUID Type and Generation

Sequential integer keys work well in one database, but they have limits. They are generated by the database, so a client cannot know a row's id before inserting it. Two databases (or two shards) generate the same numbers. And they reveal information: /orders/1042 tells anyone roughly how many orders exist and lets them guess other ids. A universally unique identifier (UUID) is a 128-bit value that any system can generate independently, with a negligible chance of collision.

PostgreSQL's uuid type stores the 16 bytes in binary (not as a 36-character string), compares them quickly, and validates input:

SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;
SELECT 'A0EEBC999C0B4EF8BB6D6BB9BD380A11'::uuid;     -- also accepted: no hyphens, upper case
SELECT 'not-a-uuid'::uuid;
-- ERROR:  invalid input syntax for type uuid: "not-a-uuid"

SELECT pg_column_size('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);   -- 16 bytes
SELECT octet_length('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::text);     -- 36 bytes, plus a length header

Versions. The UUID standard (RFC 9562) defines several versions. Two matter in practice:

Version	Content	Generator in PostgreSQL	Index behavior
v4	122 random bits	gen_random_uuid() (13+), uuidv4() (18+)	New values land at random places in the B-tree
v7	48-bit Unix timestamp in milliseconds, then random bits	uuidv7() (18+); an extension or the application before that	New values are appended near the end, like a sequence

 v4: 7f3a91c2-...  b14e0c77-...  2d9f40aa-...    random order -> inserts scattered across the whole index
 v7: 01928a6e-1c..  01928a6e-1d..  01928a6e-20..   time order   -> inserts go to the rightmost index page

Random v4 keys hurt write performance on large tables. Each insert touches a random index page, so the working set is the whole index rather than its recent end, which causes more cache misses, more page splits, and more WAL. Time-ordered v7 keys keep the locality of a sequence while still being generated anywhere.

-- PostgreSQL 18
CREATE TABLE api_requests (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  client_id   bigint NOT NULL,
  payload     jsonb
);

SELECT uuid_extract_version(id),      -- 7 (function available since PostgreSQL 17)
       uuid_extract_timestamp(id)     -- creation time embedded in the key
FROM api_requests LIMIT 1;

-- PostgreSQL 13-17: random UUIDs are built in
CREATE TABLE sessions (
  id  uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

UUID or bigint?

Concern	bigint identity	uuid (v7)
Size	8 bytes	16 bytes (also in every FK and index)
Generated by	The database only	Database, application, or offline client
Merging data from several databases	Conflicts	No conflicts
Guessable / leaks volume	Yes	No guessable sequence, but v7 reveals creation time
Human-friendly (support tickets, URLs)	Yes	No

A common hybrid uses a bigint primary key internally and a separate uuid column with a unique index as the public identifier in URLs and APIs.

Key takeaways

• uuid stores 128 bits in 16 bytes and validates format. Never store UUIDs as text.
• gen_random_uuid() creates random v4 UUIDs. uuidv7() (PostgreSQL 18) creates time-ordered v7 UUIDs.
• Random keys scatter inserts across the index. Prefer v7 for primary keys of large, write-heavy tables.
• UUIDs help with distributed generation and hiding sequence numbers, at the cost of size. v7 still reveals when a row was created.

🧪 Practice

1. (Beginner) Generate five v4 UUIDs and five v7 UUIDs (or simulate v7 on an older version with an extension). Sort each list and observe which one follows creation order.
2. (Intermediate) Create two tables with one million rows each, one keyed by gen_random_uuid() and one by uuidv7(). Compare insert time and the primary key index size (pg_relation_size), then explain the difference.
3. (Interview) "We expose order ids in URLs. Should we switch our primary keys to UUIDs?" Hint: Separate the internal key from the public identifier, and weigh storage cost, index locality, and what each kind of id leaks.

<a id="32-temporal-types"></a>

3.2 Temporal Types

This subchapter covers how PostgreSQL represents calendar dates, times of day, points in time, and durations, how time zones interact with each of them, and the functions and patterns that keep date logic correct across daylight saving changes and countries.

date, time, and timestamp

Dates and times look simple until you have to answer "how many days until the subscription renews?" or "which orders were placed yesterday?". Storing them as text ('07/01/2024') makes those questions impossible to answer reliably: the format is ambiguous (July 1 or January 7?), invalid dates such as February 30 slip in, and sorting text does not sort chronologically. Dedicated temporal types validate input, sort correctly, and support date arithmetic.

Type	Stores	Storage	Resolution	Example
date	A calendar day	4 bytes	1 day	2024-07-01
time (without time zone)	A time of day	8 bytes	1 microsecond	14:30:00
time with time zone (timetz)	A time of day plus a UTC offset	12 bytes	1 microsecond	14:30:00+02
timestamp (without time zone)	A date and a time, with no zone	8 bytes	1 microsecond	2024-07-01 14:30:00
timestamp with time zone (timestamptz)	An absolute point in time	8 bytes	1 microsecond	2024-07-01 12:30:00+00
interval	A duration	16 bytes	1 microsecond	1 day 02:00:00

This topic covers date, time, and timestamp. The next topic covers timestamptz, and a later one covers interval.

date is a calendar day with no time and no time zone: a birthday, a due date, a public holiday. It is the right type whenever the day is the fact, not a moment.

time is a time of day, without a date: a shop's opening time, a daily job's schedule. timetz attaches a fixed UTC offset to a time of day, but an offset without a date is nearly meaningless (is Rome at +01 or +02? it depends on the date). The PostgreSQL documentation itself discourages timetz.

timestamp (without time zone) is a date plus a time as written on a wall clock, with no information about where. 2024-07-01 09:00 could be 9 AM in Tokyo or in New York, which are 13 hours apart. It is right for a few cases (see Storing and Querying Time Zones Correctly), but it is usually the wrong choice for recording when something happened.

-- Input: ISO 8601 (YYYY-MM-DD) is unambiguous and always accepted
SELECT date '2024-07-01';
SELECT '2024-02-30'::date;
-- ERROR:  date/time field value out of range: "2024-02-30"

-- Arithmetic
SELECT date '2024-03-01' - date '2024-02-01';      -- 29: date - date = integer number of days
SELECT date '2024-03-01' - 1;                      -- 2024-02-29: date - integer = date
SELECT date '2024-07-01' + time '09:30';           -- timestamp 2024-07-01 09:30:00
SELECT time '23:30' + interval '1 hour';           -- 00:30:00: time of day wraps around midnight

-- Precision: an optional number of fractional digits (0-6)
SELECT timestamp '2024-07-01 10:00:00.1234567';    -- 2024-07-01 10:00:00.123457: rounded to microseconds
SELECT '2024-07-01 10:00:00.789'::timestamp(0);    -- 2024-07-01 10:00:01: rounded, not truncated

-- A timestamp silently ignores a zone in its input
SELECT timestamp '2024-07-01 12:00:00+02';         -- 2024-07-01 12:00:00 (the +02 is discarded)

Special values. PostgreSQL accepts 'infinity' and '-infinity' for date and timestamps. They sort after and before every other value, which is convenient for "valid until further notice" columns. The inputs 'today', 'tomorrow', 'yesterday', 'now', and 'epoch' (1970-01-01 00:00 UTC) are converted to a value when the literal is parsed. Do not use DEFAULT 'now' in a table definition. Use DEFAULT now() or DEFAULT current_date, which are evaluated at each insert.

CREATE TABLE subscriptions (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  starts_on   date NOT NULL DEFAULT current_date,     -- evaluated per insert
  ends_on     date NOT NULL DEFAULT 'infinity',       -- open-ended, but still comparable
  renewal_at  time NOT NULL DEFAULT '03:00',          -- daily batch time
  CHECK (starts_on < ends_on)
);

SELECT * FROM subscriptions WHERE current_date BETWEEN starts_on AND ends_on;   -- works for open-ended rows

Date output format. The display format is controlled by the DateStyle setting (ISO, MDY by default). Keep it at ISO and format dates for humans with to_char() in the presentation layer, never by changing what the database outputs.

Key takeaways

• Use date for calendar days, time for times of day, and avoid timetz.
• timestamp without time zone is a wall-clock reading with no location. It ignores any offset given in its input.
• Timestamps have microsecond resolution. timestamp(0) and similar round to fewer fractional digits.
• 'infinity' works well for open-ended dates. 'now' and 'today' as literals are frozen at parse time, so use now() and current_date in defaults.

🧪 Practice

1. (Beginner) Compute your age in days with current_date - date 'YYYY-MM-DD', and the date 100 days from today.
2. (Intermediate) A legacy table stores dates as text in the format DD/MM/YYYY, and some rows are invalid (31/02/2023). Write a migration that converts the column to date, reporting the invalid rows instead of failing. (Hint: a DO block, or a function that catches the exception.)
3. (Interview) "A developer stores created_at as timestamp without time zone and fills it with the application server's local time. What can go wrong?" Hint: Think about servers in several regions, daylight saving transitions that repeat an hour, and moving the server to another zone.

timestamptz and Time Zone Handling

When an order is placed, a payment captured, or a log line written, you want to record an instant, one specific moment on the global timeline, regardless of where the observer stands. That is what timestamp with time zone (timestamptz) stores. Its name is misleading: it does not store a time zone. It converts the input to UTC, stores 8 bytes of UTC microseconds, and converts back to the session's time zone for display.

An analogy: timestamptz is a single event broadcast live worldwide. Viewers in London and Tokyo see different clock times on their walls, but it is the same moment. timestamp without time zone is a note that says "meet at 9:00" with no city written on it.

 Input                                   Stored (UTC)            Shown with TimeZone = ...
 '2024-07-01 12:00:00+02'      ---->     2024-07-01 10:00 UTC    'UTC'               2024-07-01 10:00:00+00
                                                                 'America/New_York'  2024-07-01 06:00:00-04
                                                                 'Asia/Tokyo'        2024-07-01 19:00:00+09

The TimeZone setting controls how timestamptz values are shown and how input without an explicit zone is interpreted. It can be set per server, database, role, or session:

SET TimeZone = 'UTC';
SELECT timestamptz '2024-07-01 12:00:00+02';      -- 2024-07-01 10:00:00+00

SET TimeZone = 'America/New_York';
SELECT timestamptz '2024-07-01 12:00:00+02';      -- 2024-07-01 06:00:00-04: same instant, different display
SELECT timestamptz '2024-07-01 12:00:00';         -- no zone in input: interpreted as New York time

ALTER DATABASE shop SET TimeZone = 'UTC';         -- default for new sessions on this database
SHOW TimeZone;

Drivers usually set the session time zone or convert to the language's own instant type (java.time.Instant, Python datetime with tzinfo), so applications see correct values regardless of display.

Converting with AT TIME ZONE. This operator converts between the two timestamp types, and its direction depends on the input type:

Expression	Input type	Result type	Meaning
tstz AT TIME ZONE 'Asia/Tokyo'	timestamptz	timestamp	"What did the wall clock in Tokyo show at this instant?"
ts AT TIME ZONE 'Asia/Tokyo'	timestamp	timestamptz	"This wall-clock reading was in Tokyo. Which instant is that?"

SET TimeZone = 'UTC';

-- Instant -> local wall-clock time
SELECT timestamptz '2024-07-01 10:00:00+00' AT TIME ZONE 'Asia/Tokyo';
-- 2024-07-01 19:00:00   (a timestamp without time zone)

-- Local wall-clock time -> instant
SELECT timestamp '2024-07-01 09:00' AT TIME ZONE 'America/New_York';
-- 2024-07-01 13:00:00+00   (a timestamptz)

-- Converting a wall-clock time from one zone to another needs both steps
SELECT (timestamp '2024-07-01 12:00' AT TIME ZONE 'Europe/Rome') AT TIME ZONE 'America/New_York';
-- 2024-07-01 06:00:00

Time zone names. Use IANA names such as 'Europe/Rome' or 'America/New_York'. They contain the full history of daylight saving rules. Avoid abbreviations ('EST', 'CET'): they are fixed offsets, so 'EST' is wrong for New York half the year. Avoid POSIX-style offsets such as 'UTC+5': their sign is the opposite of ISO 8601 ('UTC+5' means five hours behind UTC), which surprises almost everyone. The view pg_timezone_names lists the available names with their current offsets.

Current time functions. PostgreSQL has several, and the difference matters inside long transactions:

Function	Value	Changes within a transaction
now(), current_timestamp, transaction_timestamp()	Start of the current transaction	No
statement_timestamp()	Start of the current statement	Per statement
clock_timestamp()	Actual current time	Per call, even within one statement
localtimestamp, current_date	Like now(), as timestamp / date in the session time zone	No

BEGIN;
SELECT now(), clock_timestamp();     -- almost equal
SELECT pg_sleep(2);
SELECT now(), clock_timestamp();     -- now() is unchanged; clock_timestamp() moved 2 seconds
COMMIT;

Every row inserted in one transaction gets the same now(), which is usually what you want for consistent created_at values. Use clock_timestamp() to measure durations inside a transaction.

Key takeaways

• timestamptz stores an instant (as UTC). It does not remember the zone the input came from.
• The session TimeZone setting controls display and the interpretation of input without an offset.
• AT TIME ZONE converts timestamptz to local wall-clock timestamp, and timestamp to timestamptz, depending on the input type.
• Use IANA zone names, never abbreviations or POSIX offsets.
• now() is fixed for the whole transaction. clock_timestamp() is the real current time.

🧪 Practice

1. (Beginner) Insert now() into a timestamptz column. Display it with TimeZone set to 'UTC', 'Asia/Kolkata', and 'America/Los_Angeles', and confirm the stored value never changes (compare extract(epoch from col)).
2. (Intermediate) A meeting is at 09:00 in 'Europe/London' on 2024-03-31 (the day the UK switches to summer time). Compute the instant in UTC and the local time in 'America/Chicago', which changed to summer time on a different date. Repeat for 2024-03-20.
3. (Interview) "Explain why timestamp with time zone is the recommended default even though it doesn't store a time zone." Hint: Think about what an event time needs to answer (ordering, durations, comparisons across regions) versus what it needs to display.

Interval Type and Arithmetic

Business rules are full of durations: a 30-day trial, a 2-hour reservation, a monthly billing cycle, an account locked for 15 minutes. The interval type represents these durations so they can be added to dates and timestamps directly. The subtle part is that "one month" and "one day" are not fixed amounts of time: months have 28 to 31 days, and a day across a daylight saving change has 23 or 25 hours.

To handle this, PostgreSQL stores an interval as three separate fields: months, days, and microseconds. They are only combined when the interval is applied to a specific date.

 interval '1 year 2 months 10 days 03:00:00'
   months       = 14          (1 year = 12 months)
   days         = 10
   microseconds = 10,800,000,000   (3 hours)

Input forms

SELECT interval '1 year 2 months 3 days 04:05:06';   -- PostgreSQL verbose format
SELECT interval 'P1Y2M3DT4H5M6S';                    -- ISO 8601 duration: same value
SELECT interval '90 minutes';                        -- 01:30:00
SELECT interval '1.5 days';                          -- 1 day 12:00:00: fractions spill into the smaller field
SELECT make_interval(days => 10, hours => 2);        -- 10 days 02:00:00: safe with parameters
SELECT 15 * interval '1 minute';                     -- build intervals from numbers

Arithmetic on calendars. Adding months moves the calendar month and keeps the day if possible. If the day does not exist, it uses the last day of the month. This makes month arithmetic not associative:

SELECT date '2024-01-31' + interval '1 month';                         -- 2024-02-29 (leap year)
SELECT date '2024-01-31' + interval '1 month' + interval '1 month';    -- 2024-03-29
SELECT date '2024-01-31' + interval '2 months';                        -- 2024-03-31: different!

To compute the n-th monthly billing date, always add n * interval '1 month' to the original anchor date, never one month to the previous result.

Days versus hours across daylight saving time. With timestamptz, adding '1 day' keeps the same local wall-clock time, while '24 hours' adds exactly 24 elapsed hours:

SET TimeZone = 'America/New_York';      -- clocks go back one hour on 2024-11-03

SELECT timestamptz '2024-11-03 00:00' + interval '1 day';     -- 2024-11-04 00:00:00-05 (25 hours later)
SELECT timestamptz '2024-11-03 00:00' + interval '24 hours';  -- 2024-11-03 23:00:00-05

Use '1 day' for "same time tomorrow" (reminders, daily schedules), and hours or minutes for elapsed-time rules (a 24-hour lockout, a token that expires in 3600 seconds).

Subtraction and normalization

SELECT timestamptz '2024-07-03 18:00+00' - timestamptz '2024-07-01 12:00+00';
-- 2 days 06:00:00: subtracting timestamps gives days + time, never months

SELECT age(date '2024-03-15', date '2023-01-20');   -- 1 year 1 mon 26 days: calendar-aware difference
SELECT justify_hours(interval '36 hours');          -- 1 day 12:00:00
SELECT justify_days(interval '35 days');            -- 1 mon 5 days (assumes 30-day months)

-- Total length in seconds
SELECT extract(epoch from interval '1 day 2 hours');   -- 93600
SELECT extract(epoch from interval '1 month');          -- 2592000: assumes 30 days, an approximation

Example: lockouts and trials

CREATE TABLE plans (
  code          text PRIMARY KEY,
  trial_length  interval NOT NULL CHECK (trial_length >= interval '0')   -- '14 days', '1 month'
);

CREATE TABLE login_attempts (
  user_id       bigint      NOT NULL,
  failed_at     timestamptz NOT NULL DEFAULT now()
);

-- Is the user locked out? (5 failures in the last 15 minutes)
SELECT count(*) >= 5 AS locked
FROM login_attempts
WHERE user_id = 42
  AND failed_at > now() - interval '15 minutes';     -- elapsed time: minutes, not days

Comparing intervals uses the same 30-day-month approximation, so interval '1 month' = interval '30 days' is true even though adding each to January 31 gives different dates.

Key takeaways

• An interval has three independent fields: months, days, and microseconds.
• Month arithmetic clamps to the end of the month and is not associative. Always compute from an anchor date.
• With timestamptz, '1 day' keeps the local clock time across daylight saving changes. '24 hours' is exactly 24 elapsed hours.
• Subtracting timestamps gives days and time. Use age() for a calendar-aware difference in years and months.
• Conversions between months and days (justify_days, extract(epoch ...), comparisons) assume 30-day months.

🧪 Practice

1. (Beginner) Compute the date 1 month, 3 months, and 1 year after 2024-02-29, and the number of days between today and next New Year's Day.
2. (Intermediate) Generate the next 12 monthly billing dates for a subscription that started on 2024-01-31, so that every date is the last day of its month when the 31st does not exist, without drift. Compare your result with generate_series(start, end, interval '1 month').
3. (Interview) "A user in Berlin sets a daily reminder for 08:00. Should you store the reminder interval as '1 day' or '24 hours'?" Hint: Consider what happens on the night clocks change, and which zone the arithmetic is evaluated in.

Date and Time Functions

Reports group sales by month, dashboards bucket events into 5-minute windows, and invoices print dates in a local format. PostgreSQL has a broad set of functions for taking timestamps apart, rounding them, building them, and formatting them. Knowing the right one avoids error-prone string manipulation.

Extracting fields

SELECT extract(year    FROM date '2024-07-07');    -- 2024
SELECT extract(quarter FROM date '2024-07-07');    -- 3
SELECT extract(doy     FROM date '2024-07-07');    -- 189: day of the year
SELECT extract(dow     FROM date '2024-07-07');    -- 0: Sunday = 0 ... Saturday = 6
SELECT extract(isodow  FROM date '2024-07-07');    -- 7: ISO, Monday = 1 ... Sunday = 7
SELECT extract(week    FROM date '2024-07-07');    -- 27: ISO week number
SELECT extract(epoch   FROM timestamptz '2024-07-01 00:00+00');   -- 1719792000: Unix seconds

extract() returns numeric (since PostgreSQL 14). The older date_part() returns double precision. On timestamptz, fields such as hour and day are computed in the session time zone.

Truncating and binning

SET TimeZone = 'UTC';
SELECT date_trunc('month', timestamptz '2024-07-15 10:00+00');           -- 2024-07-01 00:00:00+00
SELECT date_trunc('week',  date '2024-07-07');                           -- 2024-07-01 00:00:00+00: weeks start on Monday
SELECT date_trunc('day', timestamptz '2024-07-15 03:00+00', 'Asia/Tokyo'); -- midnight in Tokyo (PostgreSQL 12+)

-- Arbitrary bucket widths (PostgreSQL 14+): 15-minute buckets aligned to an origin
SELECT date_bin('15 minutes', timestamp '2024-07-01 10:37:12', timestamp '2000-01-01');   -- 2024-07-01 10:30:00

Building values

SELECT make_date(2024, 2, 29);                                    -- 2024-02-29
SELECT make_timestamp(2024, 7, 1, 9, 30, 0);                      -- timestamp
SELECT make_timestamptz(2024, 7, 1, 9, 0, 0, 'Europe/Rome');      -- 09:00 in Rome as an instant
SELECT to_timestamp(1719842709);                                  -- Unix epoch seconds -> timestamptz
SELECT to_timestamp('2024-07-01 14:05', 'YYYY-MM-DD HH24:MI');    -- parse with an explicit format
SELECT to_date('01/07/2024', 'DD/MM/YYYY');                       -- 2024-07-01

Formatting with to_char

Pattern	Meaning	Example output
YYYY, MM, DD	Year, month, day (zero-padded)	2024, 07, 01
HH24, HH12, MI, SS	Hours, minutes, seconds	14, 02, 05, 09
Month, Mon, Day, Dy	Names (padded to fixed width)	July , Jul, Monday , Mon
FM prefix	Fill mode: removes padding	FMMonth -> July
TZ, OF	Zone abbreviation, UTC offset	CEST, +02
IYYY, IW	ISO year and week	2024, 27

SELECT to_char(date '2024-07-01', 'FMDay, DD FMMonth YYYY');      -- Monday, 01 July 2024
SELECT to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SSOF');              -- quoted text is copied literally

Series and overlaps

-- One row per day, including days with no sales (left join a calendar)
SELECT d::date AS day, coalesce(sum(o.total), 0) AS revenue
FROM generate_series(date '2024-07-01', date '2024-07-07', interval '1 day') AS d
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

-- Do two periods overlap? Intervals are half-open: touching endpoints do not overlap
SELECT (date '2024-07-01', date '2024-07-10') OVERLAPS (date '2024-07-10', date '2024-07-20');   -- false

Range types (3.3) handle overlaps more completely and can be indexed.

Functions in indexes and the sargability trap. A condition like WHERE extract(year FROM created_at) = 2024 cannot use a plain index on created_at, because the index is sorted by created_at, not by the function's result. Rewrite such filters as ranges on the raw column:

-- Slow: computes extract() for every row
SELECT count(*) FROM orders WHERE extract(year FROM created_at) = 2024;

-- Fast: an index range scan on created_at
SELECT count(*) FROM orders
WHERE created_at >= timestamptz '2024-01-01 00:00 UTC'
  AND created_at <  timestamptz '2025-01-01 00:00 UTC';

Expression indexes are possible only on immutable functions. date_trunc('day', timestamptz) is only stable, because its result depends on the session TimeZone, so it cannot be indexed directly. The three-argument form with an explicit zone, or (created_at AT TIME ZONE 'UTC'), is immutable.

Key takeaways

• extract() takes timestamps apart. dow counts from Sunday = 0, isodow from Monday = 1.
• date_trunc() rounds down to a unit. date_bin() rounds down to any bucket width.
• Use to_char() and to_timestamp() / to_date() with explicit formats, and make_* functions to build values from parts.
• generate_series() with a calendar table fills gaps in time-series reports.
• Filter with ranges on the raw column rather than functions of it, so indexes can be used.

🧪 Practice

1. (Beginner) For today's date, output: the ISO week number, the name of the weekday, the first day of the current month, and the last day of the current month.
2. (Intermediate) Given an events(occurred_at timestamptz) table, write a query that counts events in 10-minute buckets for the last 2 hours, including buckets with zero events.
3. (Interview) "A query filters with WHERE created_at::date = current_date and is slow despite an index on created_at. Why, and how do you fix it?" Hint: Think about what the index is sorted by, and how to express "today" as a range in the right time zone.

Storing and Querying Time Zones Correctly

Most time-related bugs do not come from the types themselves but from mixing up three different kinds of time information: when something happened, what time it was locally, and what local time something is planned for. Each needs a different storage strategy. Getting it wrong shows up as reports that are off by a few hours, appointments that shift by an hour after a daylight saving change, or "yesterday's" sales that include part of today.

Three kinds of temporal data

What you are storing	Example	Store as
A past or exact instant	Order placed, payment captured, log entry	timestamptz
A future local event	A dentist appointment at 10:00 in Rome next March	timestamp (wall clock) + text IANA zone name
A floating local time	"Alarm at 07:00 wherever I am", store opening hours	time or timestamp without zone

Why not store future events as timestamptz? Governments change time zone rules, sometimes with only weeks of notice. If an appointment at "10:00 in Rome" was converted to UTC a year in advance, and the rules then change, the stored instant now corresponds to 09:00 or 11:00 local time, even though the meeting is still at 10:00. Storing the wall-clock time and the zone lets you compute the instant with the current rules whenever it is needed.

 Booked in 2024:   10:00 Europe/Rome on 2026-03-30  ->  converted to UTC with 2024's rules = 08:00 UTC
 Rules change in 2025 (hypothetically, no more summer time)
 In 2026:          08:00 UTC  ->  shows as 09:00 Rome   (wrong: the meeting is still at 10:00)
 Stored as (2026-03-30 10:00, 'Europe/Rome')          ->  converted with current rules: correct

Example: events with a local schedule

CREATE TABLE appointments (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  local_start timestamp   NOT NULL,                         -- wall-clock time as agreed with the customer
  time_zone   text        NOT NULL
    CHECK (now() AT TIME ZONE time_zone IS NOT NULL),       -- rejects unknown zone names on write
  duration    interval    NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()            -- an instant: when the booking was made
);

INSERT INTO appointments (local_start, time_zone, duration)
VALUES ('2026-03-30 10:00', 'Europe/Rome', interval '45 minutes');

-- The real instant, computed with the time zone rules installed today
SELECT id,
       local_start AT TIME ZONE time_zone AS starts_at      -- timestamp + zone -> timestamptz
FROM appointments;

A CHECK that calls now() is technically non-immutable. It works here because it only validates the zone name, but a trigger or a lookup against pg_timezone_names is a stricter alternative.

Users and local days. Store each user's IANA zone (users.time_zone text). When a report asks for "orders per day", decide whose day: the business's, the customer's, or UTC. Then convert explicitly instead of relying on the session setting:

-- Daily revenue in the business's local days (Europe/Rome), with timestamptz storage
SELECT (o.created_at AT TIME ZONE 'Europe/Rome')::date AS local_day,
       sum(o.total) AS revenue
FROM orders o
WHERE o.created_at >= timestamptz '2024-07-01 00:00 Europe/Rome'    -- range bounds in the same zone
  AND o.created_at <  timestamptz '2024-08-01 00:00 Europe/Rome'
GROUP BY local_day
ORDER BY local_day;

-- Indexable "per local day" lookups: AT TIME ZONE with a constant zone is immutable
CREATE INDEX orders_local_day_idx ON orders (((created_at AT TIME ZONE 'Europe/Rome')::date));

Half-open ranges. Always filter time periods as >= start AND < end. BETWEEN '2024-07-01' AND '2024-07-31' on a timestamp column misses everything after midnight on July 31, and BETWEEN ... AND '2024-07-31 23:59:59' misses the fractional second before midnight.

Rules of thumb

• Keep the server and database TimeZone at 'UTC', so logs, psql sessions, and scripts all agree. Convert at the edges: in queries for reports, or in the application for display.
• Store instants as timestamptz, never as timestamp filled with "local time" or UTC by convention. The convention gets lost; the type does not.
• Store zones as IANA names, never as offsets (+02:00): an offset loses the daylight saving rules.
• Keep the time zone database up to date. PostgreSQL ships its own copy or uses the operating system's (--with-system-tzdata), so update minor versions or OS packages regularly.
• Remember that local times can be ambiguous (repeated hour in autumn) or nonexistent (skipped hour in spring). PostgreSQL resolves them deterministically, but the business should decide what an appointment at 02:30 on such a day means.

-- 02:30 does not exist in New York on 2024-03-10 (clocks jump 02:00 -> 03:00)
SELECT timestamp '2024-03-10 02:30' AT TIME ZONE 'America/New_York';
-- 2024-03-10 07:30:00+00, displayed as 03:30 local: PostgreSQL moves it forward

Key takeaways

• Past events and exact instants: timestamptz. Future local events: wall-clock timestamp plus an IANA zone name.
• Decide whose "day" a report uses, and convert with AT TIME ZONE explicitly instead of relying on the session.
• Filter time periods with half-open ranges (>= and <), with bounds expressed in the right zone.
• Run servers in UTC, store zone names, not offsets, and keep tzdata current.

🧪 Practice

1. (Beginner) Store three orders placed at 23:30 UTC, 00:30 UTC, and 22:30 UTC on consecutive days. Group them by day in UTC, in 'Asia/Tokyo', and in 'America/Los_Angeles', and explain why the groups differ.
2. (Intermediate) Design a schema for recurring weekly classes ("every Tuesday at 18:00 in the studio's zone") and write a query that lists the next four occurrences of each class as UTC instants. Test it across a daylight saving change.
3. (Interview) "Our mobile app shows meetings an hour off for some users after the clocks changed. Where would you look?" Hint: Check what type and zone each layer stores (database, API payload, client), whether offsets were stored instead of zone names, and when local times were converted to instants.

<a id="33-composite-and-collection-types"></a>

3.3 Composite and Collection Types

This subchapter covers PostgreSQL types that go beyond a single scalar value: arrays of values, composite types that group fields, ranges and multiranges that represent spans, enumerated types with a fixed set of labels, and domains that attach constraints to an existing type.

Arrays and Array Operators

Sometimes a value is naturally a short list: the tags on a blog post, the phone numbers of a contact, the feature vector of a sample. The textbook relational answer is a separate child table (2.3), and it is still the right answer when you need to join on the elements, reference them with foreign keys, or attach data to each one. But when the list is always read and written as a whole and you only need to ask "does it contain X?", a child table adds joins and writes for no benefit. PostgreSQL lets any type be stored as an array.

CREATE TABLE posts (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text   NOT NULL,
  tags   text[] NOT NULL DEFAULT '{}'              -- an array of text; '{}' is the empty array
);

INSERT INTO posts (title, tags) VALUES
  ('Intro to SQL',    ARRAY['sql', 'beginner']),   -- ARRAY constructor
  ('Indexing deep dive', '{sql,performance,postgres}');   -- array literal syntax

Indexing and slicing. Arrays are 1-based. Out-of-range subscripts return NULL rather than raising an error.

SELECT (ARRAY[10, 20, 30])[1];       -- 10
SELECT (ARRAY[10, 20, 30])[2:3];     -- {20,30}: a slice, inclusive on both ends
SELECT (ARRAY[10, 20, 30])[0];       -- NULL: there is no element 0
SELECT (ARRAY[10, 20, 30])[5];       -- NULL, not an error

SELECT array_length(ARRAY[10, 20, 30], 1);   -- 3: the length of dimension 1
SELECT array_length('{}'::int[], 1);         -- NULL for an empty array (a common surprise)
SELECT cardinality('{}'::int[]);             -- 0: total number of elements, safer for emptiness checks

Operators and functions

Operator / function	Meaning	Example	Result
||, array_append, array_prepend	Concatenate / add an element	ARRAY[1,2] || 3	{1,2,3}
array_remove(a, x)	Remove all occurrences	array_remove(ARRAY[1,2,1], 1)	{2}
array_position(a, x)	First index of x	array_position(ARRAY['a','b'], 'b')	2
x = ANY (a)	Is x an element?	'sql' = ANY (tags)	true / false
a @> b	Does a contain all of b?	tags @> ARRAY['sql','postgres']	true / false
a <@ b	Is a contained in b?	ARRAY['sql'] <@ tags	true / false
a && b	Do they share any element?	tags && ARRAY['go','rust']	true / false
unnest(a)	Turn an array into rows	unnest(ARRAY['a','b'])	two rows
array_agg(x)	Turn rows into an array	array_agg(name ORDER BY name)	{...}

-- Posts tagged with both 'sql' and 'postgres'
SELECT title FROM posts WHERE tags @> ARRAY['sql', 'postgres'];

-- Posts with any beginner-friendly tag
SELECT title FROM posts WHERE tags && ARRAY['beginner', 'tutorial'];

-- Tag popularity: unnest turns each element into its own row
SELECT tag, count(*)
FROM posts, unnest(tags) AS tag             -- implicit LATERAL: one row per (post, tag)
GROUP BY tag
ORDER BY count(*) DESC;

-- Keep element positions with WITH ORDINALITY
SELECT * FROM unnest(ARRAY['a', 'b']) WITH ORDINALITY AS t(tag, pos);   -- (a,1), (b,2)

-- Pass a list of ids as a single parameter from an application
SELECT * FROM posts WHERE id = ANY ($1::bigint[]);

Indexing. A B-tree index on an array column only helps for comparing whole arrays. For containment and overlap (@>, <@, &&), create a GIN index, which indexes each element separately (6.2):

CREATE INDEX posts_tags_gin ON posts USING gin (tags);
-- Used by: tags @> ARRAY['sql']   tags && ARRAY['x','y']
-- NOT used by: 'sql' = ANY (tags)  -> rewrite it as tags @> ARRAY['sql']

NULL inside arrays. An array can contain NULL elements, and = ANY follows three-valued logic: 3 = ANY('{1,NULL}') is NULL, not false. Add CHECK (array_position(tags, NULL) IS NULL) if nulls should never appear.

When not to use arrays

Use an array when	Use a child table when
The list is small and read or written as a whole	Elements are added or removed individually and often
You query "contains" or "overlaps"	You need foreign keys on elements (e.g. tag ids referencing tags)
Order matters and is part of the value	Each element has its own attributes (who added a tag, when)
The elements are scalars	You need uniqueness or constraints per element across rows

Multidimensional arrays exist (int[][]), but they must be rectangular, and PostgreSQL does not enforce declared dimensions or sizes. int[3] is treated exactly like int[].

Key takeaways

• Any type can be an array (text[], int[]). Arrays are 1-based, and out-of-range subscripts return NULL.
• Use @>, <@, and && for containment and overlap, and back them with a GIN index.
• unnest() turns arrays into rows and array_agg() turns rows into arrays.
• = ANY($1) is a clean way to pass a variable-length list as one parameter.
• Arrays fit small, whole-value lists. Use a child table when elements need foreign keys, their own attributes, or frequent individual updates.

🧪 Practice

1. (Beginner) Create the posts table above with five rows. Find posts that have the tag 'sql', posts with no tags at all, and the number of tags of each post.
2. (Intermediate) Write an UPDATE that adds a tag to a post only if it is not already present, and another that renames the tag 'postgres' to 'postgresql' in every post. Then create a GIN index and use EXPLAIN to confirm it is used for @> but not for = ANY.
3. (Interview) "Would you store a user's roles as a text[] column or in a user_roles table?" Hint: Think about referential integrity with a roles table, auditing grants, queries like "all users with role X", and how often roles change.

Composite Row Types

Some values naturally have several parts that always travel together: an address (street, city, postal code, country), a money amount (value and currency), a geographic coordinate. A composite type groups named fields into one type, like a struct in C or a record in other languages. Every table in PostgreSQL also defines a composite type with the same name as the table, describing one of its rows. That is what makes it possible to pass whole rows to functions and return rows from them.

CREATE TYPE address AS (
  street       text,
  city         text,
  postal_code  text,
  country      char(2)
);

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

INSERT INTO customers (name, billing, shipping) VALUES
  ('Ada', ROW('1 Main St', 'Rome', '00100', 'IT'),   -- ROW constructor
          ('2 Side St', 'Milan', '20100', 'IT'));     -- ROW keyword is optional with 2+ fields

Accessing fields. Wrap the column in parentheses, otherwise PostgreSQL reads billing.city as "column city of table billing":

SELECT name, (billing).city, (shipping).postal_code FROM customers;

-- Updating one field: no parentheses on the left side of SET
UPDATE customers SET billing.city = 'Turin' WHERE id = 1;

-- Expand all fields into separate columns
SELECT (billing).* FROM customers;

Tables as row types. A table name used as a value refers to the whole row:

SELECT c FROM customers c;                       -- one column holding the entire row
SELECT row_to_json(c) FROM customers c;          -- {"id":1,"name":"Ada","billing":{"street":...}}

-- A function that takes a row of the customers table
CREATE FUNCTION shipping_label(c customers) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT c.name || E'\n' || (coalesce(c.shipping, c.billing)).street
              || E'\n' || (coalesce(c.shipping, c.billing)).city
$$;

SELECT shipping_label(c) FROM customers c;
SELECT c.shipping_label FROM customers c;        -- functional notation: looks like a computed column

Row comparisons. Row values compare field by field, left to right. This is useful for keyset pagination over several columns (4.1):

SELECT ROW(1, 2) < ROW(1, 3);                    -- true: first fields equal, so the second decides
SELECT * FROM events
WHERE (created_at, id) > ('2024-07-01 10:00+00', 1042)    -- "after this row" in (created_at, id) order
ORDER BY created_at, id
LIMIT 50;

Limitations. Composite types work well as function parameters and results, and for grouping values in queries. As column types, they have drawbacks that make many teams prefer plain columns:

Concern	Composite column	Separate columns
Constraints on fields	Not in the type itself. Use a domain over the composite type or a CHECK on the column	Normal NOT NULL, CHECK, FK per column
Foreign keys on a field	Not possible	Possible (country -> countries)
Indexing a field	Expression index on ((billing).city)	Plain index
ORM and driver support	Limited, often parsed as text	Universal
Changing the type	ALTER TYPE ... ADD ATTRIBUTE affects every user of the type	Per table

-- Constraints via a domain over the composite (PostgreSQL 11+)
CREATE DOMAIN valid_address AS address
  CHECK ((VALUE).city IS NOT NULL AND (VALUE).country ~ '^[A-Z]{2}$');

Key takeaways

• A composite type groups named fields. Every table is also a composite type for its rows.
• Access a field with (col).field, update one with SET col.field = ..., and expand all with (col).*.
• Row comparisons go field by field and are handy for multi-column pagination.
• Composite columns cannot carry per-field constraints or foreign keys directly. Prefer separate columns in tables and use composites for function interfaces.

🧪 Practice

1. (Beginner) Create a money_amount composite type with amount numeric(12,2) and currency char(3), use it in an invoices table, and list invoices whose amount is over 100 EUR.
2. (Intermediate) Write a function full_name(p people) RETURNS text and call it both as full_name(p) and as p.full_name. Then create an index on the expression and check with EXPLAIN whether a WHERE p.full_name = '...' query uses it.
3. (Interview) "When would you use a composite type instead of separate columns or a jsonb column?" Hint: Compare schema enforcement, constraint support, tooling, and how often the structure changes.

Range and Multirange Types

A hotel booking runs from check-in to check-out. A price is valid from one date until another. A discount applies to orders between 100 and 500 EUR. Each of these is a span, and modeling it with two columns (start, end) makes common questions awkward: "does this booking overlap that one?" needs four comparisons and careful handling of open ends, and nothing stops end < start. A range type stores the whole span as one value, with operators for containment and overlap, and with index and constraint support.

Built-in range types

Range type	Element type	Multirange type (14+)
int4range	integer	int4multirange
int8range	bigint	int8multirange
numrange	numeric	nummultirange
daterange	date	datemultirange
tsrange	timestamp	tsmultirange
tstzrange	timestamptz	tstzmultirange

Bounds. Each end is either inclusive ([, ]) or exclusive ((, )), and either end can be unbounded (omitted). The default and recommended form is half-open [start, end), where the start is included and the end is not. Half-open ranges tile perfectly: [09:00, 10:00) and [10:00, 11:00) touch without overlapping. For discrete types (int4range, daterange), PostgreSQL normalizes every range to [) form.

 [2024-07-01, 2024-07-05)     includes July 1, 2, 3, 4  (check-out day not included)
 [2024-07-01, )               from July 1, no end
 empty                        contains nothing

SELECT daterange('2024-07-01', '2024-07-05');           -- [2024-07-01,2024-07-05): default bounds '[)'
SELECT int4range(1, 10, '[]');                          -- [1,11): normalized to half-open
SELECT numrange(1.5, 2.5, '(]');                        -- (1.5,2.5]: continuous types keep their bounds
SELECT '[2024-07-01,)'::daterange;                      -- unbounded above

SELECT lower(r), upper(r), upper_inf(r), isempty(r)
FROM (SELECT '[2024-07-01,)'::daterange AS r) t;        -- 2024-07-01, NULL, true, false

Operators

Operator	Meaning	Example	Result
@>	Contains element or range	int4range(1,10) @> 5	true
<@	Is contained by	5 <@ int4range(1,10)	true
&&	Overlaps	int4range(1,5) && int4range(5,8)	false (half-open)
-|-	Adjacent	int4range(1,5) -|- int4range(5,8)	true
*	Intersection	int4range(1,5) * int4range(3,8)	[3,5)
+	Union (must be contiguous)	int4range(1,5) + int4range(3,8)	[1,8)
<<, >>	Strictly left / right of	int4range(1,3) << int4range(5,8)	true

Example: bookings that cannot overlap

The killer feature of ranges is the exclusion constraint (2.2): "no two rows for the same room may have overlapping periods", enforced by the database even under concurrent inserts:

CREATE EXTENSION IF NOT EXISTS btree_gist;     -- lets GiST handle '=' on plain scalar columns like room_id

CREATE TABLE room_bookings (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  int       NOT NULL,
  during   tstzrange NOT NULL CHECK (NOT isempty(during)),
  EXCLUDE USING gist (room_id WITH =, during WITH &&)   -- same room AND overlapping period -> conflict
);

INSERT INTO room_bookings (room_id, during) VALUES (1, '[2024-07-01 09:00+00, 2024-07-01 10:00+00)');
INSERT INTO room_bookings (room_id, during) VALUES (1, '[2024-07-01 10:00+00, 2024-07-01 11:00+00)');  -- OK: touching
INSERT INTO room_bookings (room_id, during) VALUES (1, '[2024-07-01 09:30+00, 2024-07-01 10:30+00)');
-- ERROR:  conflicting key value violates exclusion constraint "room_bookings_room_id_during_excl"

-- Which bookings cover a given instant? A GiST index makes this fast
CREATE INDEX room_bookings_during_idx ON room_bookings USING gist (during);
SELECT * FROM room_bookings WHERE during @> timestamptz '2024-07-01 09:15+00';

PostgreSQL 18 adds SQL-standard temporal keys: PRIMARY KEY (room_id, during WITHOUT OVERLAPS) creates the same kind of constraint, and foreign keys can reference such keys with PERIOD, so that a child's period must lie within its parent's.

Multiranges. The union of two ranges that do not touch cannot be a single range. A multirange is an ordered set of non-overlapping ranges, useful for availability calendars, opening hours with breaks, or "all the periods a subscription was active":

SELECT int4range(1, 3) + int4range(5, 8);
-- ERROR:  result of range union would not be contiguous

SELECT '{[1,3), [5,8)}'::int4multirange @> 6;                         -- true

-- range_agg merges many ranges into one multirange
SELECT range_agg(r)
FROM (VALUES (int4range(1,3)), (int4range(2,5)), (int4range(8,9))) AS t(r);   -- {[1,5),[8,9)}

-- Free time = the working day minus booked slots
SELECT tstzmultirange(tstzrange('2024-07-01 09:00+00', '2024-07-01 17:00+00'))
       - range_agg(during) AS free
FROM room_bookings
WHERE room_id = 1;

Key takeaways

• Ranges store a span as one value with inclusive or exclusive bounds. Prefer half-open [start, end).
• Use @>, &&, and -|- for containment, overlap, and adjacency, backed by GiST indexes.
• An exclusion constraint with && prevents overlapping periods, safely under concurrency. PostgreSQL 18's WITHOUT OVERLAPS keys express the same idea in standard SQL.
• Multiranges represent sets of non-contiguous spans. range_agg() builds them, and - subtracts ranges from them.

🧪 Practice

1. (Beginner) Create a price_history(product_id, price, valid daterange) table. Insert three consecutive price periods for one product and query the price valid on a given date.
2. (Intermediate) Add an exclusion constraint so a product cannot have two overlapping price periods. Then write a single transaction that changes the price from tomorrow: close the current open-ended period and insert a new one.
3. (Interview) "How would you guarantee that no two meetings in the same room overlap, even when two users book at the same instant?" Hint: Compare checking in the application with a SELECT first, locking, and a constraint that the database enforces with an index.

Enumerated Types

Many columns accept only a short, fixed list of values: an order status (pending, paid, shipped), a T-shirt size, a priority. Plain text accepts typos ('shiped'). A CHECK (status IN (...)) constraint or a lookup table with a foreign key both work (2.4). An enumerated type is a third option: a custom type whose values are a declared, ordered list of labels.

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

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid');
INSERT INTO orders (status) VALUES ('lost');
-- ERROR:  invalid input value for enum order_status: "lost"

Ordering. Enum values sort in declaration order, not alphabetically. That makes comparisons meaningful:

SELECT 'paid'::order_status < 'shipped';            -- true: declared earlier
SELECT * FROM orders WHERE status >= 'shipped';     -- everything shipped or later
SELECT * FROM orders ORDER BY status;               -- workflow order, not alphabetical
SELECT enum_range(NULL::order_status);              -- {pending,paid,shipped,delivered}

Each value is stored in 4 bytes, labels are case-sensitive, and an enum is its own type: comparing it to text needs a cast, and two different enum types cannot be compared with each other.

Changing an enum

ALTER TYPE order_status ADD VALUE 'cancelled';                     -- appended at the end
ALTER TYPE order_status ADD VALUE 'refunded' BEFORE 'cancelled';   -- positioned explicitly
ALTER TYPE order_status RENAME VALUE 'delivered' TO 'completed';
-- There is no DROP VALUE: removing a label means creating a new type and converting the column

Adding a value is fast and does not rewrite tables. Inside a transaction, a newly added value cannot be used until the transaction commits:

BEGIN;
ALTER TYPE order_status ADD VALUE 'on_hold';
INSERT INTO orders (status) VALUES ('on_hold');
-- ERROR:  unsafe use of new value "on_hold" of enum type order_status
-- HINT:  New enum values must be committed before they can be used.
ROLLBACK;

This matters for migration tools that run each migration in one transaction: put ADD VALUE in its own migration.

Enum, check constraint, or lookup table?

Need	ENUM	CHECK (col IN (...))	Lookup table + FK
Add a value	ALTER TYPE ... ADD VALUE (cheap)	Replace the constraint (validates the table)	INSERT a row
Remove a value	Recreate the type	Replace the constraint	DELETE a row (if unused)
Meaningful custom order	Yes, built in	No	Yes, with a sort_order column
Extra attributes (label, description, active flag)	No	No	Yes
Values changed by non-developers	No	No	Yes
Storage per row	4 bytes	Size of the text	Size of the key

Enums fit stable, developer-owned lists where order matters, such as workflow states. Lookup tables fit lists that change often, need translations or metadata, or are managed through an admin interface.

Key takeaways

• CREATE TYPE ... AS ENUM (...) defines a fixed, ordered set of labels, stored in 4 bytes.
• Enums sort by declaration order, so < and ORDER BY follow the workflow.
• Values can be added and renamed, but not removed. A new value is unusable until its transaction commits.
• Prefer a lookup table when values change often or need attributes of their own.

🧪 Practice

1. (Beginner) Create a ticket_priority enum (low, medium, high, critical) and a tickets table. Select tickets with priority high or above, ordered from most to least urgent.
2. (Intermediate) Remove the value medium from the enum: write a migration that creates a new type, maps existing medium tickets to low, converts the column with ALTER COLUMN ... TYPE ... USING, and drops the old type.
3. (Interview) "Your team debates an enum versus a lookup table for country. What do you recommend?" Hint: Think about how many values there are, who changes them and how often, whether they need extra data (names, currency, dialing code), and how the choice affects migrations.

Domains as Constrained Types

The same rule often appears in many tables: email addresses must contain an @, amounts must be positive, a country code is two upper-case letters, a percentage is between 0 and 100. Repeating the same CHECK constraint in ten tables invites drift: one copy gets updated and the others do not. A domain is a named type based on an existing type, with constraints attached. Define the rule once, then use the domain as a column type everywhere.

Think of a domain as a subtype: an email_address is text, but not every text is an email_address.

CREATE DOMAIN email_address AS text
  CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');     -- VALUE refers to the value being checked

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

CREATE DOMAIN country_code AS char(2)
  CHECK (VALUE ~ '^[A-Z]{2}$');

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

CREATE TABLE customers (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email    email_address NOT NULL UNIQUE,
  country  country_code  NOT NULL
);

CREATE TABLE invoices (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total     positive_amount NOT NULL,
  discount  percentage      NOT NULL DEFAULT 0
);

INSERT INTO invoices (total) VALUES (-5);
-- ERROR:  value for domain positive_amount violates check constraint "positive_amount_check"

Domains also apply to casts, function parameters, and PL/pgSQL variables, so a function declared with a positive_amount parameter rejects invalid arguments.

Changing a domain's rules. Constraints are added and removed on the domain, and apply to every column that uses it:

-- Add a rule without checking existing rows immediately (quick, minimal locking)...
ALTER DOMAIN email_address ADD CONSTRAINT email_lowercase CHECK (VALUE = lower(VALUE)) NOT VALID;
-- ...then validate existing data separately
ALTER DOMAIN email_address VALIDATE CONSTRAINT email_lowercase;

ALTER DOMAIN email_address DROP CONSTRAINT email_lowercase;

The NOT NULL trap. A domain can be declared NOT NULL, but the domain cannot stop expressions from producing NULL values of its type. An outer join, for example, fills missing columns with NULL, regardless of the domain:

CREATE DOMAIN required_amount AS numeric NOT NULL;
-- SELECT p.amt FROM a LEFT JOIN payments p ON ...  -> can still return NULL of type required_amount

The PostgreSQL documentation recommends putting NOT NULL on the column instead, as in the tables above, and keeping domains for value rules (CHECK).

Domains versus alternatives

Approach	Reuse	Change in one place	Type-level documentation	Limits
CHECK on each column	No	No	No	Duplicated rules drift
Domain	Yes	Yes	Yes (the column's type says what it is)	CHECK only: no foreign keys, no cross-column rules
Lookup table + FK	Yes	Yes	Partly	Only for finite value lists

A domain cannot reference other columns or tables. Rules like "end after start" stay as table CHECK constraints, and rules that involve other rows need exclusion constraints or triggers.

Key takeaways

• A domain is an existing type plus constraints, defined once and reused as a column type.
• Domain checks apply everywhere the type appears: columns, casts, function parameters, variables.
• ALTER DOMAIN ... ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT adds rules safely to existing data.
• Put NOT NULL on columns rather than on domains.
• Domains express single-value rules only. Cross-column or cross-row rules need table constraints or triggers.

🧪 Practice

1. (Beginner) Create a us_postal_code domain that accepts 12345 and 12345-6789, use it in a table, and test valid and invalid inserts.
2. (Intermediate) Three tables each have a phone column with slightly different CHECK constraints. Write a migration that introduces a phone_e164 domain (+ followed by 8 to 15 digits), finds rows that would violate it, and converts all three columns.
3. (Interview) "Should business rules live in database domains and constraints or in application code?" Hint: Consider multiple applications writing to the same database, bulk loads and manual fixes, error messages for users, and which rules are stable enough to encode in the schema.

<a id="34-semi-structured-data"></a>

3.4 Semi-Structured Data

This subchapter covers data whose structure is not fixed by the schema: JSON in its two PostgreSQL forms, the operators, path language, and SQL-standard functions for querying it, how to index it, when a document model is a better fit than tables, and PostgreSQL's support for XML.

json vs. jsonb

Some data does not fit neatly into fixed columns: webhook payloads from third parties, user preferences that differ per user, product attributes that vary by category (a laptop has RAM and a shirt has a size). Adding a nullable column for every possible attribute does not scale, and the entity-attribute-value pattern (attribute_name, attribute_value rows) makes queries painful. PostgreSQL can store JSON documents directly in a column, and query, index, and validate them, so a table can mix strict relational columns with flexible document fields.

There are two JSON types. Both reject invalid JSON. They differ in how the document is stored:

Aspect	json	jsonb
Storage	The exact input text	A decomposed binary format
Whitespace and key order	Preserved	Not preserved (keys are sorted by length, then bytes)
Duplicate keys	All kept	Last value wins
Insert speed	Faster (only validated)	Slightly slower (parsed and converted)
Read and query speed	Reparsed on every access	Fast: no reparsing
Equality operator (=)	No	Yes
Containment (@>), existence (?), jsonpath match (@@)	No	Yes
GIN indexing	No	Yes

SELECT '{"b": 1, "a": 2, "a": 3}'::json;    -- {"b": 1, "a": 2, "a": 3}: kept verbatim
SELECT '{"b": 1, "a": 2, "a": 3}'::jsonb;   -- {"a": 3, "b": 1}: duplicate removed, keys reordered

SELECT '{"a":1}'::jsonb = '{ "a" : 1 }'::jsonb;    -- true: formatting does not matter
SELECT '{"x": 1e2}'::jsonb;                        -- {"x": 100}: numbers are stored as numeric
SELECT '{"x": 1.50}'::jsonb;                       -- {"x": 1.50}: trailing zeros are kept

Use jsonb by default. Choose json only when the exact original text matters, for example to verify a signature computed over a webhook body, or for a write-only log of payloads that are never queried.

JSON types and SQL types. JSON has its own scalar types: string, number, boolean, and null. A JSON null is a value inside the document and is different from SQL NULL, which means the whole column (or a missing key) is absent:

SELECT '{"a": null}'::jsonb -> 'a';           -- null (a JSON null: a jsonb value, not SQL NULL)
SELECT '{"a": null}'::jsonb -> 'a' IS NULL;   -- false
SELECT '{"a": null}'::jsonb ->> 'a' IS NULL;  -- true: converted to text, JSON null becomes SQL NULL
SELECT '{"a": null}'::jsonb -> 'b' IS NULL;   -- true: a missing key gives SQL NULL
SELECT jsonb_typeof('{"a": [1]}'::jsonb -> 'a');   -- array

Example: a hybrid table

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text          NOT NULL UNIQUE,                  -- relational: always present, constrained
  name        text          NOT NULL,
  price       numeric(10,2) NOT NULL CHECK (price >= 0),
  category    text          NOT NULL,
  attributes  jsonb         NOT NULL DEFAULT '{}'             -- flexible: varies by category
    CHECK (jsonb_typeof(attributes) = 'object')               -- at least require an object at the top
);

INSERT INTO products (sku, name, price, category, attributes) VALUES
  ('LAP-01', 'Ultrabook 14', 1299.00, 'laptop', '{"ram_gb": 16, "cpu": "M3", "ports": ["usb-c", "hdmi"]}'),
  ('TSH-01', 'Logo Tee',       19.90, 'apparel', '{"size": "M", "color": "navy"}');

jsonb values over about 2 KB are compressed and stored out of line (TOAST, 6.1). Updating any part of a document rewrites the whole value, so large documents that change often are expensive.

Key takeaways

• json stores the original text. jsonb stores a parsed binary form that is faster to query and can be indexed.
• jsonb drops whitespace and duplicate keys and does not keep key order.
• Use jsonb unless you must keep the exact input text.
• A JSON null is not SQL NULL. ->> converts JSON null to SQL NULL.
• Combine relational columns for stable, constrained fields with a jsonb column for the variable parts.

🧪 Practice

1. (Beginner) Insert the same document with different whitespace, key order, and a duplicate key into a json and a jsonb column. Compare what each returns and whether = works.
2. (Intermediate) Add a CHECK constraint to the products table that requires attributes to contain a numeric ram_gb key when category = 'laptop'.
3. (Interview) "When would you choose json over jsonb?" Hint: Think about what jsonb normalizes away and which use cases need the original bytes.

JSON Operators and Path Expressions

Once documents are stored, you need to reach inside them: read a nested field, filter on a value deep in an array, or modify one key without rewriting the document in application code. PostgreSQL offers two complementary tools: operators for direct navigation, and SQL/JSON path expressions (jsonpath), a small query language for documents, similar to XPath for XML.

Navigation operators

Operator	Returns	Example (on data)	Result
-> key or index	jsonb	data -> 'address'	{"city": "Rome", "zip": "00100"}
->> key or index	text	data ->> 'name'	Ada
#> path array	jsonb	data #> '{address,city}'	"Rome"
#>> path array	text	data #>> '{tags,0}'	vip
[...] subscript (14+)	jsonb	data['address']['city']	"Rome"

-- Sample document
-- {"name": "Ada", "age": 36, "active": true,
--  "address": {"city": "Rome", "zip": "00100"}, "tags": ["vip", "beta"]}

SELECT data ->> 'name'                    AS name,       -- text
       data -> 'address' ->> 'city'       AS city,       -- chain: jsonb, then text
       data -> 'tags' -> -1               AS last_tag,   -- negative index counts from the end: "beta"
       (data ->> 'age')::int + 1          AS next_age    -- cast text to a SQL type for arithmetic
FROM users_docs;

Use -> while navigating and ->> at the last step when you want a SQL value. Missing keys and out-of-range indexes return SQL NULL, not an error.

Containment and existence operators (jsonb only, and indexable with GIN):

Operator	Question	Example	Result
@>	Does the left document contain the right one?	data @> '{"address": {"city": "Rome"}}'	true
<@	Is the left contained in the right?	'{"age": 36}' <@ data	true
?	Does this key (or array string element) exist at the top level?	data ? 'tags'	true
?|	Does any of these keys exist?	data ?| array['x', 'age']	true
?&	Do all of these keys exist?	data ?& array['x', 'age']	false

Containment is structural: {"tags": ["vip"]} is contained in a document whose tags array includes "vip" among other elements.

Modifying documents

UPDATE users_docs SET data = data || '{"age": 37}';                         -- merge: add or replace top-level keys
UPDATE users_docs SET data = data - 'tags';                                 -- remove a top-level key
UPDATE users_docs SET data = data #- '{address,zip}';                       -- remove a nested key by path
UPDATE users_docs SET data = jsonb_set(data, '{address,city}', '"Milan"');  -- set a nested value (must be valid JSON)
UPDATE users_docs SET data['address']['city'] = '"Naples"';                 -- subscript assignment (14+)
SELECT jsonb_insert('{"t": ["a"]}', '{t,0}', '"z"');                        -- {"t": ["z", "a"]}: insert into an array

|| is a shallow merge: data || '{"address": {"zip": "20100"}}' replaces the whole address object.

SQL/JSON path expressions. A jsonpath starts at $ (the document root) and navigates with .key, [index], and [*] (all elements). A filter ? (...) keeps items that satisfy a condition, with @ meaning the current item:

 $.tags[*]                         every element of tags
 $.items[*] ? (@.qty > 3).sku      sku of items whose qty is over 3
 $.address.city                    nested key
 $.** ? (@ == "vip")               "vip" at any depth
 $.items.size()                    a method: length of an array

SELECT jsonb_path_query(data, '$.tags[*]') FROM users_docs;                -- one row per tag
SELECT jsonb_path_query_array(
  '{"items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 5}]}',
  '$.items[*] ? (@.qty > 3).sku');                                          -- ["B"]

-- Boolean tests, usable in WHERE and indexable
SELECT * FROM users_docs WHERE data @? '$.tags[*] ? (@ == "vip")';   -- does the path return any item?
SELECT * FROM users_docs WHERE data @@ '$.age > 30';                  -- is the predicate true?

-- Variables keep user input out of the path string
SELECT * FROM users_docs
WHERE jsonb_path_exists(data, '$.age ? (@ >= $min)', jsonb_build_object('min', 18));

Turning documents into rows

SELECT * FROM jsonb_each_text('{"a": 1, "b": "x"}');                   -- key/value rows
SELECT jsonb_array_elements_text(data -> 'tags') FROM users_docs;        -- one row per array element
SELECT * FROM jsonb_to_recordset('[{"sku":"A","qty":2},{"sku":"B","qty":5}]')
  AS x(sku text, qty int);                                              -- typed columns from an array of objects

Key takeaways

• -> returns jsonb and ->> returns text. Chain -> and finish with ->>, then cast for arithmetic.
• @> (containment) and ? (key existence) are the workhorse filters, and both can use GIN indexes.
• Modify documents with ||, -, #-, jsonb_set, or subscript assignment. || merges only at the top level.
• jsonpath ($.a[*] ? (@.x > 1)) handles filters over arrays and nested data. @? and @@ use it in WHERE.
• Pass values into paths as variables rather than building path strings from user input.

🧪 Practice

1. (Beginner) Using the products table from the previous topic, list each laptop's name and RAM as an integer, ordered by RAM.
2. (Intermediate) An orders table has a data jsonb column with an items array of {sku, qty, unit_price} objects. Write one query that returns orders containing at least one item with qty > 10, using jsonpath, and another that computes each order's total from the array.
3. (Interview) "What is the difference between data -> 'a' = '1' and data @> '{"a": 1}', and why might one be much faster?" Hint: Think about which operators a GIN index supports and what each expression compares.

SQL/JSON Functions

PostgreSQL's original JSON functions (jsonb_build_object, jsonb_path_query, and others) are specific to PostgreSQL. The SQL standard (SQL:2016 and SQL:2023) defines its own JSON functions, and PostgreSQL implements them in recent versions. They are worth learning because they are portable across databases (Oracle, Db2, MySQL support many of them), and because they handle type conversion, empty results, and errors more explicitly than the native functions.

Group	Functions	Available since
Predicate	IS JSON [VALUE | OBJECT | ARRAY | SCALAR] [WITH UNIQUE KEYS]	PostgreSQL 16
Constructors	JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG	PostgreSQL 16
Query functions	JSON_EXISTS, JSON_VALUE, JSON_QUERY	PostgreSQL 17
Table function	JSON_TABLE	PostgreSQL 17
Conversion	JSON(), JSON_SCALAR(), JSON_SERIALIZE()	PostgreSQL 17

Validating text as JSON

SELECT '{"a": 1}' IS JSON;                         -- true
SELECT '{"a": ' IS JSON;                           -- false: no exception, unlike a cast
SELECT '[1]' IS JSON OBJECT, '[1]' IS JSON ARRAY;  -- false, true
SELECT '{"a": 1, "a": 2}' IS JSON WITH UNIQUE KEYS;   -- false

-- Useful when importing raw text
CREATE TABLE raw_webhooks (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body  text NOT NULL CHECK (body IS JSON OBJECT)
);

Building JSON

SELECT JSON_OBJECT('id': 1, 'name': 'Ada');                        -- {"id" : 1, "name" : "Ada"}
SELECT JSON_ARRAY(1, 'a', NULL);                                   -- [1, "a"]: NULLs skipped by default
SELECT JSON_ARRAY(1, 'a', NULL NULL ON NULL);                      -- [1, "a", null]
SELECT JSON_OBJECT('d': current_date RETURNING jsonb);             -- choose the output type

-- Aggregates: one JSON value per group
SELECT c.id,
       JSON_ARRAYAGG(o.id ORDER BY o.created_at) AS order_ids,
       JSON_OBJECTAGG(o.status: o.total)         AS total_by_status   -- key: value
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Note the defaults: JSON_OBJECT keeps NULL values (NULL ON NULL), while JSON_ARRAY drops them (ABSENT ON NULL).

Querying JSON (PostgreSQL 17+)

Function	Returns	Use for
JSON_EXISTS(doc, path)	boolean	Does the path match anything?
JSON_VALUE(doc, path [RETURNING type])	One SQL scalar	Extract a single value with a type
JSON_QUERY(doc, path [WITH WRAPPER])	jsonb (object or array)	Extract a fragment of the document

SELECT
  JSON_VALUE(attributes, '$.ram_gb' RETURNING int)                       AS ram_gb,
  JSON_VALUE(attributes, '$.cpu' DEFAULT 'unknown' ON EMPTY)             AS cpu,      -- missing key -> default
  JSON_QUERY(attributes, '$.ports')                                       AS ports,    -- an array, as jsonb
  JSON_QUERY(attributes, '$.ports[*]' WITH WRAPPER)                       AS ports2,   -- several items wrapped in []
  JSON_EXISTS(attributes, '$.ports[*] ? (@ == "hdmi")')                   AS has_hdmi
FROM products;

-- Errors are configurable: by default a failed conversion returns NULL; ERROR ON ERROR raises it
SELECT JSON_VALUE('{"ram_gb": "lots"}', '$.ram_gb' RETURNING int);                  -- NULL
SELECT JSON_VALUE('{"ram_gb": "lots"}', '$.ram_gb' RETURNING int ERROR ON ERROR);   -- raises an error

-- PASSING binds SQL values to path variables
SELECT name FROM products
WHERE JSON_EXISTS(attributes, '$.ram_gb ? (@ >= $min)' PASSING 16 AS min);

JSON_TABLE: documents to relational rows (PostgreSQL 17+). JSON_TABLE maps parts of a document to typed columns. It replaces many combinations of jsonb_array_elements and casts, and it is the standard way to "shred" JSON into tables:

-- orders.data = {"customer": "Ada",
--                "items": [{"sku": "A1", "qty": 2, "price": 9.5},
--                          {"sku": "B7", "qty": 1, "price": 20}]}

SELECT o.id, jt.*
FROM orders o,
     JSON_TABLE(
       o.data, '$.items[*]'                      -- one output row per item
       COLUMNS (
         line_no  FOR ORDINALITY,                -- 1, 2, ... within each document
         sku      text          PATH '$.sku',
         qty      int           PATH '$.qty' DEFAULT 1 ON EMPTY,
         price    numeric(10,2) PATH '$.price'
       )
     ) AS jt;

--  id | line_no | sku | qty | price
--   1 |       1 | A1  |   2 |  9.50
--   1 |       2 | B7  |   1 | 20.00

NESTED PATH '...' COLUMNS (...) handles arrays inside arrays, producing one row per innermost element.

Key takeaways

• IS JSON validates text without raising errors and works well in CHECK constraints and data cleaning.
• JSON_OBJECT, JSON_ARRAY, and their aggregates build JSON in standard SQL. Mind their different NULL defaults.
• JSON_VALUE returns typed scalars, JSON_QUERY returns fragments, JSON_EXISTS tests paths, each with explicit ON EMPTY / ON ERROR behavior.
• JSON_TABLE turns arrays in documents into typed rows and is the standard replacement for chains of jsonb_* set-returning functions.

🧪 Practice

1. (Beginner) Write a query that builds, for each customer, a JSON object with their id, name, and an array of their order ids, using only SQL/JSON constructors.
2. (Intermediate) Given a raw_webhooks(body text) table where some rows are invalid JSON, write a query that counts valid and invalid rows, then use JSON_TABLE to load the line items from valid rows into an order_lines table.
3. (Interview) "What happens when JSON_VALUE finds a value that cannot be converted to the RETURNING type, and why might you want different behavior in a report versus a data import?" Hint: Look at the ON ERROR and ON EMPTY clauses and their defaults.

Indexing JSONB

A WHERE condition on a jsonb column without a matching index scans every document in the table. Because documents are flexible, there is no single "right" index: you can index the whole document for arbitrary containment queries, or index one specific field like an ordinary column. The choice depends on which queries you run (6.2, 6.3).

Option 1: GIN on the whole document. A GIN (Generalized Inverted Index) index maps each key and value inside the documents to the rows that contain it, like the index at the back of a book. There are two operator classes:

Operator class	Indexes	Supports	Size and speed
jsonb_ops (default)	Every key and every value separately	@>, ?, ?|, ?&, @?, @@	Larger. Answers key-existence queries
jsonb_path_ops	A hash of each path-to-value combination	@>, @?, @@ only	Usually smaller and faster for containment. No ? support

CREATE INDEX products_attrs_gin      ON products USING gin (attributes);                 -- jsonb_ops
CREATE INDEX products_attrs_path_gin ON products USING gin (attributes jsonb_path_ops);  -- pick one of the two

-- Can use the GIN index
SELECT * FROM products WHERE attributes @> '{"color": "navy"}';
SELECT * FROM products WHERE attributes @@ '$.ram_gb == 16';
SELECT * FROM products WHERE attributes ? 'warranty_years';      -- jsonb_ops only

-- Cannot use either GIN index: these are not GIN operators
SELECT * FROM products WHERE attributes ->> 'color' = 'navy';    -- rewrite as @> '{"color": "navy"}'
SELECT * FROM products WHERE (attributes ->> 'ram_gb')::int > 8; -- range: needs a B-tree (option 2)

Option 2: B-tree expression index on one field. When queries filter or sort on a specific field, especially with ranges (>, <) or ORDER BY, index that expression with a normal B-tree. It is much smaller than a GIN index over the whole document:

CREATE INDEX products_color_idx ON products ((attributes ->> 'color'));      -- note the double parentheses
CREATE INDEX products_ram_idx   ON products (((attributes ->> 'ram_gb')::int));

SELECT * FROM products WHERE attributes ->> 'color' = 'navy';               -- uses products_color_idx
SELECT * FROM products WHERE (attributes ->> 'ram_gb')::int >= 16
ORDER BY (attributes ->> 'ram_gb')::int;                                     -- uses products_ram_idx

-- The query must match the indexed expression exactly
SELECT * FROM products WHERE attributes -> 'color' = '"navy"';               -- different expression: no index

Option 3: promote hot fields to real columns. A field that almost every query uses can become a generated column, which is computed from the document, stored, and indexable like any column:

ALTER TABLE products
  ADD COLUMN color text GENERATED ALWAYS AS (attributes ->> 'color') STORED;
CREATE INDEX products_color_col_idx ON products (color);

The column always stays in sync with the document, and the planner keeps proper statistics for it, which it cannot do for fields buried inside jsonb.

Choosing

Query pattern	Index
Many different keys, ad-hoc containment filters (@>)	GIN jsonb_path_ops
Also "does key X exist?" (?, ?|, ?&)	GIN jsonb_ops
Equality, ranges, or sorting on one known field	B-tree expression index
One field used everywhere, with joins or constraints	Generated column (or a real column) + B-tree

Costs to keep in mind. GIN indexes are slower to update than B-trees. PostgreSQL buffers new entries in a "pending list" (fastupdate) to speed up writes, which is flushed by vacuum or when it fills up. Also, the planner has no detailed statistics about values inside documents, so row estimates for jsonb filters can be far off, which can lead to poor plans (7.2). Expression indexes and extended statistics on expressions help.

Key takeaways

• GIN with jsonb_ops supports containment, existence, and jsonpath operators. jsonb_path_ops is usually smaller but does not support ?.
• ->> comparisons cannot use GIN. Rewrite them as @> or create a B-tree expression index.
• B-tree expression indexes handle equality, ranges, and sorting on one field. The query must use the identical expression.
• Promote frequently used fields to generated or real columns for better indexing and statistics.

🧪 Practice

1. (Beginner) Load 100,000 rows into a jsonb table, run a containment query with EXPLAIN ANALYZE, create a GIN index, and compare the plan and timing.
2. (Intermediate) Create both a jsonb_ops and a jsonb_path_ops index on the same column and compare their sizes with pg_relation_size. Write three queries: one that both can serve, one that only jsonb_ops can serve, and one that neither can serve.
3. (Interview) "A query filters WHERE data->>'status' = 'active' on a 50-million-row table with a GIN index on data, but it still does a sequential scan. Why, and what would you do?" Hint: Check which operators GIN supports, the exact expression, and whether the filter is selective enough to benefit from an index.

Relational vs. Document Modeling Trade-offs

With jsonb, PostgreSQL can act as a document database. That raises a design question for every new entity: separate columns and tables with foreign keys, or one document holding everything? Neither is always better. Relational modeling protects integrity and supports any query you think of later. Document modeling matches how some data is created and read, and it adapts to changing shapes without migrations.

An analogy: a relational schema is a filing cabinet with labeled folders that every clerk must use correctly. It is easy to cross-reference and hard to misfile. A document is a sealed envelope with everything about one case inside. It is quick to grab as a whole, but answering "which cases mention X?" means opening many envelopes.

The same data, two ways

-- Relational: an order and its lines
CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id),
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_lines (
  order_id    bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  line_no     int    NOT NULL,
  product_id  bigint NOT NULL REFERENCES products (id),      -- integrity: the product must exist
  qty         int    NOT NULL CHECK (qty > 0),
  unit_price  numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- Document: the whole order in one value
CREATE TABLE orders_doc (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id),   -- keys and joins stay relational
  created_at   timestamptz NOT NULL DEFAULT now(),
  body         jsonb  NOT NULL                              -- {"lines": [{"product_id": 7, "qty": 2, ...}]}
);

Comparison

Concern	Relational columns and tables	jsonb document
Integrity (types, NOT NULL, FKs)	Enforced by the database	Only via CHECK constraints or triggers. No foreign keys into documents
Changing the structure	Migration (ALTER TABLE)	No migration. Old and new shapes coexist, and readers must handle both
Reading a whole aggregate	Joins	One row, one read
Ad-hoc queries and analytics	Natural: any column can be filtered, grouped, joined	Possible but verbose; statistics are weak
Updating one small field	Updates one row	Rewrites the whole document (and its TOAST data)
Storage	Compact; column names stored once in the catalog	Key names repeated in every document
Concurrency	Row-level locking per line	Two users editing different parts of one document conflict

A practical guideline: relational core, documents at the edges

• Model as columns: identifiers, foreign keys, anything you filter, join, group, or sort on regularly, and anything with integrity rules (money, statuses, dates).
• Model as jsonb: data whose shape varies per row or changes often (product attributes, user settings, feature flags per tenant), data you receive and store as-is (third-party payloads, audit snapshots), and data that is always read and written together and never queried in isolation.
• Promote a field from the document to a column as soon as it becomes important to queries or constraints.

Signs a document design is going wrong

• Queries routinely use jsonb_array_elements to join documents with other tables.
• Application code validates the same document structure in several places.
• Updates conflict because many users edit different parts of the same document.
• Documents contain ids of other rows ("product_id": 7) that can no longer be guaranteed to exist.

Key takeaways

• Relational modeling gives integrity and query flexibility. Document modeling gives schema flexibility and whole-aggregate reads.
• Keep keys, relationships, and frequently queried or constrained fields in columns.
• Use jsonb for genuinely variable or opaque data, and promote fields to columns as they become important.
• Documents cannot hold enforceable foreign keys, and every update rewrites the full value.

🧪 Practice

1. (Beginner) For each, decide between columns and jsonb, with one sentence of justification: a user's email address; a user's UI preferences; a Stripe webhook payload; an invoice total; the custom fields that each customer can define for their tickets.
2. (Intermediate) Implement both order designs above with 10,000 orders. Write the query "total quantity sold per product last month" for each, compare the SQL and the EXPLAIN ANALYZE output, and describe what happens to each design when a product is deleted.
3. (Interview) "Why use PostgreSQL with jsonb instead of a dedicated document database like MongoDB?" Hint: Consider transactions across documents and tables, joins, constraints on the relational parts, operational simplicity, and workloads where a document store might still be the better fit.

XML Type Support

Before JSON became the default for data exchange, XML filled that role, and it is still common in enterprise integrations, government and financial standards (invoicing formats, ISO 20022 payments), configuration files, and SOAP services. PostgreSQL has an xml type that checks documents are well-formed, plus functions to query them with XPath and to build XML from relational data. XML support requires PostgreSQL to be built with libxml2, which standard packages are.

Storing and validating

CREATE TABLE feeds (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc  xml NOT NULL
);

INSERT INTO feeds (doc) VALUES (
  '<catalog>
     <book id="1" lang="en"><title>SQL Basics</title><price>29.90</price></book>
     <book id="2" lang="it"><title>Basi di dati</title><price>35.00</price></book>
   </catalog>');

INSERT INTO feeds (doc) VALUES ('<catalog><book>');
-- ERROR:  invalid XML content
-- DETAIL:  line 1: Premature end of data in tag book line 1

SELECT xml_is_well_formed_document('<a><b></a>');   -- false: test without raising an error

The xml type checks well-formedness only. It does not validate documents against a schema (XSD or DTD). There are also no comparison operators on xml (= does not exist), so xml columns cannot be indexed directly. Index an expression that extracts a value instead.

Querying with XPath

-- xpath() returns an array of xml fragments
SELECT xpath('/catalog/book/title/text()', doc) FROM feeds;
-- {"SQL Basics","Basi di dati"}

-- Extract a single value and convert it
SELECT (xpath('/catalog/book[@id="2"]/price/text()', doc))[1]::text::numeric AS price FROM feeds;   -- 35.00

SELECT * FROM feeds WHERE xpath_exists('//book[@lang="it"]', doc);

XMLTABLE: XML to rows. Like JSON_TABLE, the standard XMLTABLE function maps repeated XML elements to typed rows:

SELECT x.*
FROM feeds,
     XMLTABLE('/catalog/book' PASSING doc         -- one row per <book>
       COLUMNS
         id     int     PATH '@id',               -- an attribute
         title  text    PATH 'title',             -- a child element
         price  numeric PATH 'price',
         lang   text    PATH '@lang' DEFAULT 'en'
     ) AS x;

--  id |    title     | price | lang
--   1 | SQL Basics   | 29.90 | en
--   2 | Basi di dati | 35.00 | it

Documents with namespaces need an XMLNAMESPACES(...) clause in XMLTABLE, or a namespace array as the third argument of xpath().

Producing XML from relational data

SELECT xmlelement(name catalog,
         xmlagg(
           xmlelement(name book,
             xmlattributes(b.id AS id),                  -- <book id="...">
             xmlelement(name title, b.title)             -- text is escaped automatically (& -> &amp;)
           ) ORDER BY b.id))
FROM books b;

SELECT xmlserialize(DOCUMENT doc AS text INDENT) FROM feeds;   -- pretty-printed text (PostgreSQL 16+)

Task	JSON	XML
Validate syntax	IS JSON	xml_is_well_formed_document(), the xml type
Query a value	->>, JSON_VALUE	xpath()
Shred to rows	JSON_TABLE	XMLTABLE
Build from rows	JSON_OBJECT, JSON_ARRAYAGG	xmlelement, xmlagg
Index	GIN, expression indexes	Expression indexes only

Key takeaways

• The xml type guarantees well-formedness, but not schema validity, and has no equality operator.
• Use xpath() and xpath_exists() to query, and XMLTABLE to turn repeated elements into rows.
• Build XML with xmlelement, xmlattributes, and xmlagg, which escape content correctly.
• For most new work, prefer jsonb. Use xml when an external standard or integration requires it, and extract the important values into columns.

🧪 Practice

1. (Beginner) Store the catalog document above and write queries that return all titles, the price of the book in Italian, and the number of books.
2. (Intermediate) Write a single query that produces the full <catalog> XML document from a books(id, title, price, lang) table, then use XMLTABLE to parse that output back into rows and confirm they match the table.
3. (Interview) "You must import thousands of daily XML invoices from suppliers. How would you store and query them?" Hint: Consider keeping the original document for audit, which fields to extract into columns, where schema validation happens, and how to handle malformed files.

<a id="35-specialized-types"></a>

3.5 Specialized Types

This subchapter covers types built for specific domains: IP addresses and networks, 2D geometry, full-text search documents and queries, bit strings, and the object identifier types used throughout the system catalogs. It ends with how to define your own types when nothing built in fits.

Network Address Types

Applications that deal with infrastructure, security, or analytics store IP addresses: login sources, firewall allowlists, the subnets assigned to each data center. Stored as text, an address cannot be validated, sorted numerically ('10.0.0.9' sorts after '10.0.0.10'), or tested for membership in a subnet without fragile string parsing. PostgreSQL's network types understand IPv4 and IPv6 natively.

Type	Stores	Size	Example
inet	A host address, optionally with its subnet mask	7 bytes (IPv4) or 19 bytes (IPv6)	192.168.1.10/24, 2001:db8::1
cidr	A network: no bits may be set to the right of the mask	Same as inet	192.168.1.0/24
macaddr	A 6-byte MAC address	6 bytes	08:00:2b:01:02:03
macaddr8	An 8-byte (EUI-64) MAC address	8 bytes	08:00:2b:ff:fe:01:02:03

The difference between inet and cidr: an inet is "this host, which lives in this network". A cidr is "this network" and rejects values that name a host:

SELECT '192.168.1.10/24'::inet;     -- 192.168.1.10/24: host .10 in a /24 network
SELECT '192.168.1.10'::inet;        -- 192.168.1.10: no mask means /32 (a single host)
SELECT '192.168.1.0/24'::cidr;      -- 192.168.1.0/24
SELECT '192.168.1.10/24'::cidr;
-- ERROR:  invalid cidr value: "192.168.1.10/24"
-- DETAIL:  Value has bits set to right of mask.

-- Inputs are validated and normalized
SELECT '08-00-2B-01-02-03'::macaddr;   -- 08:00:2b:01:02:03
SELECT '300.1.1.1'::inet;              -- ERROR: invalid input syntax for type inet

Operators and functions

Expression	Meaning	Result
'10.1.2.3'::inet << '10.0.0.0/8'	Is strictly contained in	true
'10.0.0.0/8'::cidr >>= '10.1.2.3'	Contains or equals	true
'10.0.0.0/8'::cidr && '10.1.0.0/16'	Do the networks overlap?	true
host('192.168.1.10/24'::inet)	Address as text, without mask	192.168.1.10
network('192.168.1.10/24'::inet)	The network part	192.168.1.0/24
masklen(...), broadcast(...), family(...)	Mask length, broadcast address, 4 or 6	24, 192.168.1.255/24, 4
'192.168.1.10'::inet + 5	Address arithmetic	192.168.1.15
trunc('08:00:2b:01:02:03'::macaddr)	Manufacturer prefix (OUI)	08:00:2b:00:00:00

Example: an IP allowlist and login geolocation

CREATE TABLE allowed_networks (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant   bigint NOT NULL,
  network  cidr   NOT NULL,
  EXCLUDE USING gist (tenant WITH =, network inet_ops WITH &&)   -- no overlapping entries per tenant (needs btree_gist)
);

CREATE TABLE logins (
  user_id    bigint      NOT NULL,
  source_ip  inet        NOT NULL,
  logged_at  timestamptz NOT NULL DEFAULT now()
);

-- Is this login from an allowed network?
SELECT EXISTS (
  SELECT 1 FROM allowed_networks
  WHERE tenant = 7 AND network >>= '203.0.113.42'::inet
);

-- Logins per /24 subnet (useful for spotting abuse)
SELECT network(set_masklen(source_ip, 24)) AS subnet, count(*)
FROM logins
GROUP BY subnet
ORDER BY count(*) DESC;

-- Containment lookups use a GiST index with the inet_ops operator class
CREATE INDEX allowed_networks_gist ON allowed_networks USING gist (network inet_ops);

A plain B-tree index on an inet column supports equality and sorting, which sorts addresses numerically and keeps IPv4 before IPv6. For "which network contains this address" questions, use GiST with inet_ops.

Key takeaways

• inet stores a host address (with an optional mask), cidr stores a network, and both support IPv4 and IPv6.
• cidr rejects host bits to the right of the mask. Use network() to derive the network from an inet.
• <<, <<=, >>, >>=, and && test containment and overlap, and GiST with inet_ops indexes them.
• Use macaddr / macaddr8 for hardware addresses. Never store IPs or MACs as text.

🧪 Practice

1. (Beginner) Store ten IPv4 and IPv6 addresses in an inet column and in a text column. Sort both and explain the difference. Then list only the IPv6 addresses.
2. (Intermediate) Given a geo_blocks(network cidr, country char(2)) table of non-overlapping networks, write the query that finds the country of each row in logins, and choose an index that makes it fast.
3. (Interview) "How would you store and query IP ranges for a rate limiter or firewall in PostgreSQL?" Hint: Compare text, integers, and cidr, and think about the operators and index type for "contains this address" lookups.

Geometric Types

Some applications need simple 2D geometry: points on a floor plan, bounding boxes of shapes in a diagram editor, nearest-neighbor search in a game map. PostgreSQL has built-in geometric types for points and shapes on a flat (Euclidean) plane, with operators for distance, containment, and intersection, and GiST index support.

Type	Represents	Size	Literal
point	A point (x, y)	16 bytes	'(1,2)'
line	An infinite line	24 bytes	'{A,B,C}' for Ax + By + C = 0
lseg	A line segment	32 bytes	'((0,0),(3,4))'
box	A rectangle aligned with the axes	32 bytes	'((0,0),(2,2))'
path	Open or closed sequence of points	Variable	'[(0,0),(1,1),(2,0)]' (open)
polygon	Closed shape	Variable	'((0,0),(4,0),(4,4),(0,4))'
circle	Center and radius	24 bytes	'<(0,0),5>'

SELECT point '(1,2)' <-> point '(4,6)';                                -- 5: Euclidean distance
SELECT box '((0,0),(2,2))' @> point '(1,1)';                           -- true: box contains point
SELECT circle '<(0,0),5>' @> point '(3,4)';                            -- true: on the boundary counts
SELECT polygon '((0,0),(4,0),(4,4),(0,4))' @> point '(2,2)';           -- true
SELECT lseg '((0,0),(3,4))' # lseg '((0,4),(3,0))';                    -- (1.5,2): intersection point
SELECT area(box '((0,0),(2,3))'), length(lseg '((0,0),(3,4))');        -- 6, 5
SELECT (point '(3,7)')[0];                                             -- 3: x coordinate by subscript

Example: nearest neighbors on a store map

CREATE TABLE shelves (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label     text  NOT NULL,
  position  point NOT NULL          -- meters from the store's south-west corner
);

CREATE INDEX shelves_position_gist ON shelves USING gist (position);

-- The 5 shelves closest to the customer: an index-ordered "K nearest neighbors" scan
SELECT label, position <-> point '(12.5, 30)' AS distance_m
FROM shelves
ORDER BY position <-> point '(12.5, 30)'
LIMIT 5;

-- Everything inside an area (uses the same index)
SELECT label FROM shelves WHERE position <@ box '((10,25),(20,35))';

The plan for the first query is an Index Scan ... Order By: (position <-> ...): GiST returns rows in distance order, so PostgreSQL does not compute and sort all distances.

Built-in types versus PostGIS. The built-in types work on a flat plane with plain numeric coordinates. They know nothing about the Earth's curvature, coordinate reference systems, or meters versus degrees. For real geographic data (addresses, GPS tracks, "stores within 5 km"), use the PostGIS extension (13.2):

Need	Built-in geometric types	PostGIS
Floor plans, diagrams, game boards, simple 2D math	Good fit	Works, but heavier
Latitude and longitude, distances in meters	Wrong results (treats degrees as flat units)	geography type, correct on the sphere
Map projections, standard formats (GeoJSON, WKT)	No	Yes
Complex operations (union, buffer, simplify)	No	Hundreds of functions

Key takeaways

• PostgreSQL has built-in point, line, lseg, box, path, polygon, and circle types on a flat plane.
• <-> computes distance, @> / <@ test containment, and && tests overlap.
• A GiST index supports containment queries and fast nearest-neighbor ordering with ORDER BY ... <-> ... LIMIT n.
• For geographic data on the Earth, use PostGIS instead of the built-in types.

🧪 Practice

1. (Beginner) Create a point column with 10 random points between (0,0) and (100,100). Find the point closest to (50,50) and all points inside the box from (0,0) to (25,25).
2. (Intermediate) Insert 100,000 random points, then compare EXPLAIN ANALYZE for a 10-nearest-neighbors query with and without a GiST index.
3. (Interview) "A developer stores store locations as point(longitude, latitude) and uses <-> to find stores within 5 km. What is wrong?" Hint: Consider units (degrees versus kilometers), how the length of a degree of longitude changes with latitude, and which tool is designed for this.

Full-Text Search Types tsvector and tsquery

Searching text with LIKE '%database%' is slow on large tables (it cannot use a normal B-tree index), and it is not very smart: it misses "databases" when you search for "database", matches "base" inside "database", and cannot rank results by relevance. Full-text search solves this by processing text linguistically: splitting it into words, discarding common words such as "the", and reducing words to a root form (stemming: "jumped", "jumping", and "jumps" all become "jump"). PostgreSQL represents the processed document as a tsvector and the processed search as a tsquery. This topic covers the two types, and 5.3 covers full-text search in depth.

tsvector: a document prepared for searching. A sorted list of distinct lexemes (normalized words), each with the positions where it occurred:

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
--  "the" and "over" are stop words and are dropped; "foxes" -> "fox", "lazy" -> "lazi" (stems, not words)

SELECT to_tsvector('simple', 'The quick brown foxes');
-- 'brown':3 'foxes':4 'quick':2 'the':1     the 'simple' configuration only lower-cases

tsquery: a search prepared for matching. Lexemes combined with & (and), | (or), ! (not), <-> (followed by), and :* (prefix):

SELECT to_tsquery('english', 'fox & (dog | cat)');           -- 'fox' & ( 'dog' | 'cat' )
SELECT plainto_tsquery('english', 'quick foxes');            -- 'quick' & 'fox': plain words, all required
SELECT phraseto_tsquery('english', 'quick brown');           -- 'quick' <-> 'brown': adjacent, in order
SELECT websearch_to_tsquery('english', '"brown fox" -cat or dog');
-- 'brown' <-> 'fox' & !'cat' | 'dog'     search-engine syntax: safe for raw user input

to_tsquery requires correct operator syntax and raises errors on malformed input. For text typed by users, use websearch_to_tsquery or plainto_tsquery.

Matching with @@

SELECT to_tsvector('english', 'The quick brown foxes jumped') @@ to_tsquery('english', 'fox & jump');   -- true
SELECT to_tsvector('english', 'database systems') @@ to_tsquery('english', 'data:*');                   -- true: prefix

Example: searchable articles

CREATE TABLE articles (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title   text NOT NULL,
  body    text NOT NULL,
  search  tsvector GENERATED ALWAYS AS (
            setweight(to_tsvector('english', title), 'A') ||   -- title matches count more (weight A)
            setweight(to_tsvector('english', body),  'B')      -- than body matches (weight B)
          ) STORED                                           -- computed on write, always in sync
);

CREATE INDEX articles_search_gin ON articles USING gin (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', 'postgres indexing') AS q
WHERE search @@ q                     -- uses the GIN index
ORDER BY rank DESC
LIMIT 10;

The configuration name ('english') must be given explicitly in the generated column, because the one-argument form of to_tsvector depends on the default_text_search_config setting and is therefore not immutable.

Full-text search versus other text matching

Technique	Finds	Index	Typical use
LIKE 'abc%'	Prefix	B-tree (with text_pattern_ops or C collation)	Autocomplete on codes
LIKE '%abc%', ILIKE	Substring	GIN with pg_trgm	Fuzzy search on short strings, names
tsvector @@ tsquery	Words, stems, phrases, with ranking	GIN (or GiST) on tsvector	Searching documents, articles, descriptions
External engine (Elasticsearch, OpenSearch)	Advanced relevance, facets, typo tolerance	Separate system	Large-scale search products

Key takeaways

• tsvector is a document as normalized lexemes with positions. tsquery is a search expression of lexemes and operators.
• Text search configurations ('english', 'simple', ...) control stemming and stop words. Always use the same configuration for documents and queries.
• @@ matches a tsvector against a tsquery. A GIN index on a stored tsvector column makes it fast.
• Use websearch_to_tsquery or plainto_tsquery for user input, and setweight with ts_rank to rank title matches higher.

🧪 Practice

1. (Beginner) Run to_tsvector('english', ...) and to_tsvector('simple', ...) on the same sentence and explain each difference in the output.
2. (Intermediate) Build the articles table with a few hundred rows (for example, from Wikipedia abstracts). Compare the results and speed of ILIKE '%term%' and full-text search for several terms, and show a case where each finds something the other misses.
3. (Interview) "When would you use PostgreSQL full-text search, and when would you move search to Elasticsearch?" Hint: Consider data volume, relevance tuning, typo tolerance, faceting, operational cost, and keeping two systems in sync.

Bit String Types

Sometimes data is naturally a set of bits: permission masks, feature flags packed together, days of the week a store is open, or fixed-width hardware status registers. PostgreSQL has two bit string types that store sequences of 1s and 0s and support bitwise operations on them.

Type	Length	On input of the wrong length
bit(n)	Exactly n bits	Error on insert. An explicit cast pads with zeros on the right or truncates
bit varying(n) / varbit(n)	Up to n bits (no limit if n is omitted)	Error if longer on insert. An explicit cast truncates

SELECT B'1010';                    -- binary literal
SELECT X'F';                       -- hex literal: 1111
SELECT B'101'::bit(4);             -- 1010: an explicit cast pads on the right
SELECT 5::bit(8);                  -- 00000101: integer to bits
SELECT B'00000101'::int;           -- 5

CREATE TABLE t (b bit(4));
INSERT INTO t VALUES (B'101');
-- ERROR:  bit string length 3 does not match type bit(4)

Bitwise operators and functions

Expression	Meaning	Result
B'1100' & B'1010'	AND	1000
B'1100' | B'1010'	OR	1110
B'1100' # B'1010'	XOR	0110
~B'1100'	NOT	0011
B'1100' << 1	Shift left (length is kept)	1000
get_bit(B'1010', 0)	Bit at position (0 = leftmost)	1
set_bit(B'1010', 1, 1)	Set a bit	1110
bit_count(B'1011')	Number of 1 bits (PostgreSQL 14+)	3

Bitwise &, |, and # require both operands to have the same length.

Example: weekly opening days

CREATE TABLE stores (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text   NOT NULL,
  open_days  bit(7) NOT NULL DEFAULT B'1111100'     -- Mon..Sun, leftmost bit = Monday
);

-- Stores open on Saturday (bit 5 when counting from 0 at the left)
SELECT name FROM stores WHERE get_bit(open_days, 5) = 1;

-- Stores open on every weekday (Mon-Fri all set)
SELECT name FROM stores WHERE open_days & B'1111100' = B'1111100';

-- Number of opening days per store
SELECT name, bit_count(open_days) FROM stores;

When to use bit strings, and when not. Bit strings are compact, but conditions like get_bit(open_days, 5) = 1 cannot use ordinary indexes, the meaning of each position lives only in documentation, and adding a flag means changing the length. Alternatives are often clearer:

Approach	Readability	Queryable and indexable per flag	Adding a flag
bit(n)	Low: positions must be documented	Only with expression indexes	Change the length
Integer bitmask (int with &)	Low	Only with expression indexes	Free (up to 32/64 bits)
Separate boolean columns	High	Yes	ADD COLUMN
text[] or enum array	High	GIN with @>	Free
Child table (one row per flag)	High	Yes, with FKs	INSERT

Key takeaways

• bit(n) stores exactly n bits, bit varying(n) stores up to n. Literals are written B'1010' or X'A'.
• &, |, #, ~, <<, >>, get_bit, set_bit, and bit_count operate on bit strings.
• Inserting a value of the wrong length into bit(n) is an error, while an explicit cast silently pads or truncates.
• Bit strings are compact but hard to read and index. Prefer booleans, arrays, or child tables unless space or an external format demands bits.

🧪 Practice

1. (Beginner) Using the stores table, add three stores with different opening days and list each store's opening days as weekday names. (Hint: generate_series(0, 6) and get_bit.)
2. (Intermediate) Write a query that finds pairs of stores with no common opening day, and another that computes the days on which every store is open, using bitwise aggregation (bit_and).
3. (Interview) "A legacy table stores user permissions as a bit(32) column. What problems would you expect, and how would you migrate it?" Hint: Think about readability, querying "all users with permission X", auditing changes, and how to map each bit position to a named permission.

Object Identifier Types

Every object in a PostgreSQL database (table, index, type, function, schema, role) is identified internally by an object identifier (OID), a 4-byte unsigned integer. The system catalogs (1.1) refer to each other by OID: pg_class lists relations, pg_attribute refers to them by attrelid, pg_index by indrelid. Reading those catalogs with raw numbers is tedious. PostgreSQL provides OID alias types (regclass, regtype, and others) that look like numbers internally but read and print as names.

Type	Refers to	Input example	Catalog
oid	Any object (raw number)	16384	Any
regclass	A relation (table, index, view, sequence)	'public.orders'	pg_class
regtype	A data type	'integer', 'jsonb'	pg_type
regproc / regprocedure	A function (by name / by name and argument types)	'now' / 'lower(text)'	pg_proc
regnamespace	A schema	'public'	pg_namespace
regrole	A role	'postgres'	pg_authid
regconfig	A text search configuration	'english'	pg_ts_config
regcollation	A collation	'"C"'	pg_collation

-- Name to OID and back: casting does the lookup, respecting search_path and quoting
SELECT 'orders'::regclass::oid;                 -- e.g. 16402
SELECT 16402::regclass;                         -- orders

-- Catalog queries read naturally
SELECT relname, relpages, reltuples
FROM pg_class
WHERE oid = 'orders'::regclass;                 -- instead of joining pg_namespace by name

SELECT attname, atttypid::regtype AS type       -- type names instead of numbers
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0 AND NOT attisdropped;

-- Many admin functions accept regclass, so a name works directly
SELECT pg_size_pretty(pg_total_relation_size('orders'));

-- Casting fails for unknown names; to_regclass returns NULL instead
SELECT 'nonexistent'::regclass;                 -- ERROR:  relation "nonexistent" does not exist
SELECT to_regclass('nonexistent');              -- NULL: safe in scripts

Other system identifier types. You will see a few more in catalogs and in system columns that every table has:

Type / column	Meaning
tableoid (system column)	OID of the table a row comes from, useful with inheritance and partitions: SELECT tableoid::regclass, * FROM measurements
ctid (type tid)	Physical location of a row version: (page, item). Changes on every update, so never use it as a key (6.1)
xmin, xmax (type xid)	Transaction ids that created and deleted the row version (8.3)
xid8	64-bit transaction id, as returned by pg_current_xact_id()
pg_lsn	A position in the write-ahead log, e.g. 0/3649F90 (11.2)

Do not use OIDs in user tables. Older PostgreSQL versions could add a hidden oid column to user tables (WITH OIDS). That option was removed in PostgreSQL 12. OIDs are 32-bit and wrap around across the whole cluster, and they differ between databases restored from a dump. Store names, or your own keys, never OIDs, in application data.

Key takeaways

• OIDs identify catalog objects. Alias types (regclass, regtype, regproc, regrole, ...) convert between OIDs and names automatically.
• 'name'::regclass is the idiomatic way to look up a table in catalog queries and admin functions.
• to_regclass() and similar functions return NULL for unknown names instead of raising an error.
• System columns tableoid, ctid, xmin, and xmax expose internals. They are useful for diagnosis, never as application keys.

🧪 Practice

1. (Beginner) For a table of your choice, list its columns with their type names using pg_attribute and regtype, and its total size with pg_total_relation_size.
2. (Intermediate) Write a query that lists every index in the public schema with its table name (indrelid::regclass), index name (indexrelid::regclass), and size, sorted by size descending.
3. (Interview) "Why should you never use ctid to identify rows in an application?" Hint: Think about what happens to a row's physical location when it is updated, when the table is vacuumed with VACUUM FULL, or when it is rebuilt.

Creating Custom Types

PostgreSQL's built-in types cover most needs, but sometimes a concept in your domain deserves its own type: an amount that always carries its currency, a range of times of day, a status workflow. Custom types put the concept into the schema, give it a name every table can share, and let the database enforce its rules. PostgreSQL is unusually extensible here (1.2): you can add types along with their operators, functions, casts, aggregates, and even index support.

Kinds of user-defined types

Kind	Created with	Typical use	Covered in
Domain	CREATE DOMAIN	An existing type plus CHECK rules	Domains as Constrained Types
Enum	CREATE TYPE ... AS ENUM	A fixed, ordered set of labels	Enumerated Types
Composite	CREATE TYPE ... AS (...)	A group of named fields	Composite Row Types
Range	CREATE TYPE ... AS RANGE	Spans over any ordered type	This topic
Base type	CREATE TYPE name (INPUT = ..., OUTPUT = ...)	A brand new storage format, written in C	This topic (overview)

Custom range types. The built-in ranges cover numbers, dates, and timestamps. For any other ordered type, define your own. A multirange type is created automatically:

-- Helper so GiST can measure distances between times (improves index quality)
CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8
LANGUAGE sql IMMUTABLE STRICT AS 'SELECT EXTRACT(EPOCH FROM (x - y))';

CREATE TYPE timerange AS RANGE (
  subtype      = time,
  subtype_diff = time_subtype_diff
);
-- Also creates the multirange type "timemultirange"

CREATE TABLE opening_hours (
  store_id  bigint     NOT NULL,
  weekday   smallint   NOT NULL CHECK (weekday BETWEEN 1 AND 7),
  hours     timerange  NOT NULL,
  EXCLUDE USING gist (store_id WITH =, weekday WITH =, hours WITH &&)   -- needs btree_gist
);

SELECT '{[09:00,12:00), [14:00,18:00)}'::timemultirange @> '15:00'::time;   -- true: open at 3 PM

Composite types with behavior. A composite type becomes much more useful with functions, operators, and casts. Here is a money type that refuses to add amounts in different currencies:

CREATE TYPE money_amount AS (amount numeric(14,2), currency char(3));

CREATE FUNCTION money_add(a money_amount, b money_amount) RETURNS money_amount
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
BEGIN
  IF a.currency <> b.currency THEN
    RAISE EXCEPTION 'currency mismatch: % vs %', a.currency, b.currency;
  END IF;
  RETURN ROW(a.amount + b.amount, a.currency)::money_amount;
END $$;

-- Operator: lets you write a + b
CREATE OPERATOR + (
  LEFTARG  = money_amount,
  RIGHTARG = money_amount,
  FUNCTION = money_add,
  COMMUTATOR = +                      -- a + b = b + a: helps the planner
);

-- Aggregate: lets you write sum(col)
CREATE AGGREGATE sum (money_amount) (
  SFUNC = money_add,                  -- state transition function, applied row by row
  STYPE = money_amount                -- state type; STRICT sfunc means the first non-null value starts the state
);

-- Cast: lets you write value::text
CREATE FUNCTION money_amount_to_text(m money_amount) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT to_char(m.amount, 'FM999G999G990D00') || ' ' || m.currency $$;
CREATE CAST (money_amount AS text) WITH FUNCTION money_amount_to_text(money_amount);

SELECT ROW(10.50, 'EUR')::money_amount + ROW(4.25, 'EUR')::money_amount;   -- (14.75,EUR)
SELECT ROW(10.50, 'EUR')::money_amount + ROW(4.25, 'USD')::money_amount;
-- ERROR:  currency mismatch: EUR vs USD
SELECT ROW(1234.5, 'EUR')::money_amount::text;                             -- 1,234.50 EUR

Base types in C. A base type defines its own on-disk representation and needs C functions for at least input (text to internal form) and output (internal form to text), plus optional binary send/receive functions. This is how extensions add types such as PostGIS geometry, citext, hstore, and vector (pgvector). Base types require superuser privileges to create and are normally distributed as extensions (13.1):

CREATE TYPE complex;                             -- 1. a "shell" type, so functions can mention it
CREATE FUNCTION complex_in(cstring) RETURNS complex
  AS 'MODULE_PATHNAME', 'complex_in' LANGUAGE C IMMUTABLE STRICT;    -- 2. C input function
CREATE FUNCTION complex_out(complex) RETURNS cstring
  AS 'MODULE_PATHNAME', 'complex_out' LANGUAGE C IMMUTABLE STRICT;   --    C output function
CREATE TYPE complex (                            -- 3. the full definition
  INTERNALLENGTH = 16,                           -- two float8 values
  INPUT  = complex_in,
  OUTPUT = complex_out,
  ALIGNMENT = double
);

Choosing the lightest tool. Each step down this list costs more to build and maintain:

1. A built-in type with a CHECK constraint.
2. A domain, when the same rule repeats across tables.
3. An enum or a composite or range type, when the concept has a structure.
4. Functions, operators, casts, and aggregates on top, when the type needs behavior.
5. A C base type (usually an existing extension), when you need a new storage format or index support.

Custom types also affect operations: drivers and ORMs may return them as text, pg_dump must include them before the tables that use them, and changing them (ALTER TYPE ... ADD ATTRIBUTE, changing a domain) affects every table at once.

Key takeaways

• PostgreSQL supports domains, enums, composites, ranges, and C base types as user-defined types.
• CREATE TYPE ... AS RANGE creates a range type (and its multirange) over any ordered subtype.
• Functions, CREATE OPERATOR, CREATE CAST, and CREATE AGGREGATE give custom types natural syntax and behavior.
• New base types require C code and are usually installed as extensions.
• Use the simplest mechanism that expresses the rule. Check client and ORM support before relying on custom types in application code.

🧪 Practice

1. (Beginner) Create a floatrange type over double precision and use it to store temperature ranges. Query which ranges contain 21.5 degrees.
2. (Intermediate) Extend money_amount with a - operator, a comparison operator < that raises an error for different currencies, and a max aggregate. Test them in a query over an invoices table.
3. (Interview) "Your team wants to add a custom money type with operators to the database. What would you consider before doing it?" Hint: Think about ORM and driver support, migrations when the type changes, performance of SQL or PL/pgSQL functions in hot paths, and whether a domain plus a currency column would be enough.

<a id="4-sql-querying-fundamentals"></a>