The PostgreSQL Type System

Beyond the standard integer/varchar/date types shared with MySQL, Postgres includes several genuinely native types with no real MySQL equivalent at all. This chapter covers the four biggest ones — and sets up the distinction postgres1-4 draws next, between structured relational richness and JSONB's own semi-structured document approach.

Arrays

Postgres allows any column to be declared as an array of any type — integer[], text[], and so on. A blog posts table could store its own tags directly:

CREATE TABLE blog_posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[]
);

INSERT INTO blog_posts (title, tags)
VALUES ('Intro to Postgres', ARRAY['databases', 'postgres', 'sql']);

-- Find posts tagged 'postgres'
SELECT * FROM blog_posts WHERE tags @> ARRAY['postgres'];

-- Expand the array into individual rows
SELECT title, unnest(tags) AS tag FROM blog_posts;

MySQL has no native array type — the standard workaround is either a proper join table, or a comma-separated string column, a well-known anti-pattern. Postgres arrays give a genuine, indexable, queryable alternative to the string-column workaround, using containment operators (@>, <@) and unnest() — but it's still worth being honest that an array column can violate normalization the same way a comma-separated string can; it's a convenience with better tooling, not a normalization loophole.

Ranges

Range types (int4range, numrange, tsrange, daterange) represent a bounded interval as a single value. A hotel booking system can store a reservation's stay as a single daterange, and check for overlap directly:

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INT,
  stay DATERANGE
);

-- Does any existing booking overlap this new stay?
SELECT * FROM bookings
WHERE room_id = 12 AND stay && DATERANGE('2026-08-01', '2026-08-05');

That single overlap operator (&&) replaces hand-written boundary logic like start1 <= end2 AND start2 <= end1. Postgres can go further and enforce this as a genuine database-level integrity guarantee via an exclusion constraint — EXCLUDE USING gist (room_id WITH =, stay WITH &&) — making it structurally impossible to insert an overlapping booking for the same room at all, something with no simple MySQL equivalent.

True ENUM Types

Postgres's CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy') creates a genuine, named, reusable type — it shows up as its own type in \d output, and any number of columns across any number of tables can be declared using it, sharing one single validated definition.

MySQL's ENUM is a column-level attribute, not a real named type — each column defines its own independent enum, even if two columns want identical values, with no cross-table reuse and no clean way to reference "the same enum" elsewhere. Postgres also allows adding a new value to an existing enum type cleanly (ALTER TYPE mood ADD VALUE 'ecstatic'), whereas altering a MySQL column-level ENUM's values typically means an actual ALTER TABLE against the column definition itself.

UUID & Network Types

Postgres has a genuine native uuid type, commonly generated with the built-in gen_random_uuid() (Postgres 13+) — useful for primary keys that don't leak sequential/positional information the way an auto-increment integer does. MySQL has no native UUID type; UUIDs there are typically stored as a CHAR(36) string or a BINARY(16), both workarounds rather than a first-class type.

Postgres also has native network types — inet (an IP address, optionally with a subnet), cidr (a network specification), and macaddr — with real, built-in operators, like checking whether an IP falls inside a CIDR block using <<. MySQL has no equivalent; these would need to be stored as plain strings or integers, with all validation left entirely to application code.

Hands-On Exercises