Numeric, Text, Boolean, Temporal, UUID, Network, Range, Multirange, and Binary Types

Learning outcomes

ServiceHub is ready to move from a teaching schema into a more realistic data model. The temptation is to choose a familiar type such as varchar for every string, bigint for every number, and timestamp for every time. PostgreSQL gives you a richer type system because the type itself can carry useful semantics: an IP address can be inet, a service window can be tstzrange, an identifier can be uuid, and an exact price should not be stored in an approximate floating-point type.

This lesson treats type selection as part of the data contract. You will insert boundary values, inspect stored values and catalog metadata, change the session time zone to expose display behavior, and intentionally provoke conversion/range errors. The goal is not to memorize PostgreSQL's type catalog; it is to learn a repeatable selection method.

1. A type is a contract, not a storage-size guess

A useful selection sequence is: meaning → valid range/precision → comparison rules → operators/indexing → portability → storage/performance. Storage size matters, but choosing the wrong semantics and then validating everything in application code creates duplicated rules and more failure paths.

2. Integers, numeric, and floating point

smallint, integer, and bigint are signed integer types with increasing range. They are excellent for counts and identifiers when the domain is integral. numeric/decimal stores exact decimal values with selectable precision/scale, while real and double precision are approximate floating-point types.

Money-like calculations usually need exact decimal behavior. Do not use double precision merely because it supports large values. Approximate representation means some decimal fractions cannot be represented exactly.

SELECT 0.1::numeric + 0.2::numeric AS exact_decimal,       0.1::double precision + 0.2::double precision AS floating_result;CREATE TABLE app.ch04_numeric_probe (    qty integer CHECK (qty >= 0),    price numeric(12,2) CHECK (price >= 0),    measurement double precision);INSERT INTO app.ch04_numeric_probe VALUES (2, 19.95, 0.1 + 0.2);SELECT * FROM app.ch04_numeric_probe;

The exact display of the floating result can vary with formatting, but its semantics remain approximate. For numeric(12,2), PostgreSQL coerces values to the declared scale and rejects values whose rounded integral part exceeds the precision budget. That behavior belongs in tests when precision is business-critical.

3. Text and Boolean values

PostgreSQL's text and varchar are both variable-length character types. A length modifier such as varchar(80) is useful only when “at most 80 characters” is a real invariant. PostgreSQL does not reward arbitrary varchar(255) declarations with a magical performance advantage over text.

CREATE TABLE app.ch04_text_probe (    summary text NOT NULL,    external_code varchar(12) NOT NULL,    is_emergency boolean NOT NULL DEFAULT false);INSERT INTO app.ch04_text_probe(summary, external_code)VALUES ('Pump inspection', 'WO-2026-001');-- Deliberately too long: should fail because the code contract says 12 characters.INSERT INTO app.ch04_text_probe(summary, external_code)VALUES ('Demo', 'THIS-CODE-IS-TOO-LONG');

The error is useful evidence that the boundary is being enforced. If truncation is genuinely desired, make it explicit in the ingest transformation and test that transformation; do not depend on surprising implicit coercion.

boolean has three logical SQL states when NULL is permitted: true, false, and unknown/null. If “unknown” is not a business state, add NOT NULL. The type alone cannot decide that policy.

4. Temporal types: an instant is not a wall-clock label

date represents a calendar date. time represents a time of day. timestamp without time zone represents a date/time field set without tracking a time-zone conversion. timestamp with time zone, commonly written timestamptz, represents an instant; PostgreSQL normalizes the input instant internally and renders it according to the session TimeZone.

SET TIME ZONE 'UTC';SELECT '2026-08-18 09:00:00+04'::timestamptz AS in_utc;SET TIME ZONE 'Asia/Baku';SELECT '2026-08-18 05:00:00+00'::timestamptz AS same_instant_baku;SET TIME ZONE 'UTC';SELECT '2026-08-18 09:00:00'::timestamp AS wall_clock_value;RESET TIME ZONE;

The first two values denote the same instant despite different displayed local times. A timestamp without time zone does not become globally unambiguous just because the application developer “assumes UTC.” If the value is an instant, store an instant and convert at presentation boundaries.

interval represents a span and supports calendar-aware fields. It is often better than encoding “90 minutes” or “3 days” as a bare integer with an undocumented unit.

SELECT timestamptz '2026-08-18 08:00+00' + interval '90 minutes' AS due_at;SELECT interval '2 days 3 hours' AS planned_duration;

5. UUIDs: uniqueness domain and ordering tradeoffs

The uuid type stores a 128-bit Universally Unique Identifier. PostgreSQL 18 can generate random UUIDv4 values with gen_random_uuid()/uuidv4() and time-ordered UUIDv7 values with uuidv7(). UUIDs are attractive when independent producers need identifiers without consulting one shared sequence.

SELECT uuidv4() AS random_uuid,       uuidv7() AS time_ordered_uuid;CREATE TABLE app.ch04_uuid_probe (    event_id uuid PRIMARY KEY DEFAULT uuidv7(),    note text NOT NULL);INSERT INTO app.ch04_uuid_probe(note) VALUES ('first'), ('second')RETURNING event_id, note;

“Distributed-friendly” does not mean “always better.” UUIDs are wider than 64-bit integers, are less friendly for humans to read, and their generation/version choices affect index locality and information exposure. UUIDv7 provides temporal ordering characteristics, but it should not be used as a secret token and its embedded time does not make it a substitute for a real event timestamp.

6. Network types carry network semantics

Storing an IP address as text throws away PostgreSQL's ability to validate it and use network operators. inet can represent a host address with an optional network prefix; cidr represents networks; macaddr/macaddr8 represent MAC addresses.

SELECT inet '192.0.2.25/24' AS host_with_prefix,       cidr '192.0.2.0/24' AS network,       inet '192.0.2.25' << cidr '192.0.2.0/24' AS contained;-- Invalid input fails instead of becoming bad text data.SELECT '999.1.1.1'::inet;

This is a recurring PostgreSQL design pattern: use a semantic type when its validation, operators, and index support match the workload. If the application must accept temporarily malformed source text for later remediation, keep the raw ingest field separate from the validated canonical field.

7. Ranges and multiranges model sets of values

A range describes a continuous span over a subtype; a multirange represents an ordered collection of non-overlapping ranges. PostgreSQL ships range/multirange pairs for integers, big integers, numeric values, timestamps, timestamptz, and dates. They are especially useful for scheduling and validity windows because containment and overlap become first-class operations.

SELECT tstzrange('2026-08-18 08:00+00', '2026-08-18 10:00+00', '[)') AS window;SELECT tstzrange('2026-08-18 08:00+00','2026-08-18 10:00+00','[)')       &&       tstzrange('2026-08-18 09:30+00','2026-08-18 11:00+00','[)')       AS windows_overlap;SELECT timestamptz '2026-08-18 09:00+00'       <@ tstzrange('2026-08-18 08:00+00','2026-08-18 10:00+00','[)')       AS instant_is_contained;SELECT '{[2026-08-18 08:00+00,2026-08-18 10:00+00),[2026-08-18 13:00+00,2026-08-18 15:00+00)}'::tstzmultirange;

The bound notation matters: [) means inclusive lower bound and exclusive upper bound. That convention makes adjacent scheduling windows compose cleanly: one can end exactly when another begins without counting the boundary twice.

8. Binary data belongs in bytea when it belongs in the database

bytea stores arbitrary bytes. It is appropriate for hashes, compact binary payloads, or small artifacts that genuinely belong in the transactional record. It is not an instruction to place every large file in a relational row; workload, backup size, streaming, deduplication, object-store integration, and access patterns still matter.

SELECT decode('deadbeef','hex') AS bytes,       encode(decode('deadbeef','hex'),'hex') AS round_trip;

9. Deliberately wrong design: strings for everything

Imagine an import table with amount text, scheduled_at text, client_ip text, and availability text. It seems flexible, but every query now repeats parsing rules; invalid values can remain latent until a report or migration fails; ordering becomes lexical instead of semantic; and indexes cannot naturally exploit type-specific operators.

A safer ingest pattern separates raw source values from validated canonical values. Keep the raw payload when audit/reprocessing requires it, but convert once into domain-appropriate columns and surface conversion failures explicitly.

CREATE TABLE app.ch04_type_lab (    work_order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    public_id uuid NOT NULL DEFAULT uuidv7(),    amount numeric(12,2) NOT NULL CHECK (amount >= 0),    scheduled_at timestamptz NOT NULL,    expected_duration interval NOT NULL CHECK (expected_duration > interval '0'),    client_address inet,    service_window tstzrange,    payload_hash bytea,    summary text NOT NULL,    is_emergency boolean NOT NULL DEFAULT false,    UNIQUE (public_id));INSERT INTO app.ch04_type_lab(amount, scheduled_at, expected_duration, client_address, service_window, payload_hash, summary)VALUES(125.50, '2026-08-18 13:00+04', interval '90 minutes', '192.0.2.25', tstzrange('2026-08-18 09:00+00','2026-08-18 11:00+00','[)'), decode('0123456789abcdef','hex'), 'Pump inspection')RETURNING work_order_id, public_id, amount, scheduled_at, client_address, service_window;

10. Hands-on lab: inspect values and metadata

1. Create app.ch04_type_lab as servicehub_owner or another disposable owner.
2. Insert one valid row and inspect it from sessions using UTC and Asia/Baku TimeZone.
3. Try an invalid inet, a negative amount, and an over-precision amount; record which layer rejects each value.
4. Use \d+ app.ch04_type_lab and the information schema to verify PostgreSQL's actual column types.
5. Use range containment/overlap operators against the service window.
6. Keep the table for the next lessons; later examples will extend the Chapter 04 model.

SELECT column_name, data_type, udt_name, numeric_precision, numeric_scale, is_nullableFROM information_schema.columnsWHERE table_schema='app' AND table_name='ch04_type_lab'ORDER BY ordinal_position;SELECT pg_typeof(public_id), pg_typeof(client_address), pg_typeof(service_window)FROM app.ch04_type_labLIMIT 1;

11. Summary and bridge

PostgreSQL's built-in types let the database reject impossible values and expose domain-specific operators. The right type does not eliminate constraints or application validation, but it narrows the state space before those higher-level rules run. In the next lesson, you will go beyond built-ins and decide when arrays, composite types, ENUMs, and domains clarify the model—and when they create unnecessary coupling.

Authoritative references

• PostgreSQL 18 — Data Types
• PostgreSQL 18 — Numeric Types
• PostgreSQL 18 — Character Types
• PostgreSQL 18 — Date/Time Types
• PostgreSQL 18 — UUID Type
• PostgreSQL 18 — UUID Functions
• PostgreSQL 18 — Network Address Types
• PostgreSQL 18 — Range Types
• PostgreSQL 18 — Binary Data Types