Advanced Data Types

PostgreSQL's advanced, non-standard-SQL data types are one of its biggest practical advantages over MySQL. This page covers the ones you'll actually reach for.

JSONB#

PostgreSQL has two JSON types: JSON (stores an exact text copy of the input, re-parsed on every read) and JSONB (stores a decomposed binary representation, slightly slower to insert but much faster to query and — critically — indexable). Use JSONB by default; there's rarely a reason to prefer plain JSON outside of needing to preserve exact original formatting or key order.

CREATE TABLE events (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    payload JSONB NOT NULL
);

INSERT INTO events (name, payload) VALUES
    ('signup', '{"user_id": 42, "plan": "pro", "referrer": {"source": "twitter"}}'),
    ('signup', '{"user_id": 43, "plan": "free", "referrer": {"source": "google"}}');

Three operators do most of the work querying JSONB:

• -> — get a JSON value by key (returns JSON/JSONB, so you can chain into nested objects).
• ->> — get a value by key as text (what you want for comparisons and output).
• @> — "contains": does the left JSONB value contain the right one as a subset?

-- -> returns JSONB (chainable); ->> returns text
SELECT payload -> 'referrer' AS referrer_json,
       payload -> 'referrer' ->> 'source' AS referrer_source
FROM events;

-- Filtering on a nested field (->> gives text, comparable with =)
SELECT * FROM events
WHERE payload -> 'referrer' ->> 'source' = 'twitter';

-- @> containment: find events where plan is "pro"
SELECT * FROM events WHERE payload @> '{"plan": "pro"}';

When JSONB beats a normalized schema, and when it doesn't#

A payload JSONB column that's queried constantly on payload ->> 'plan' is a signal that plan deserves to be a real, indexed column instead — JSONB is a relief valve for genuinely variable or sparse data, not a replacement for modeling structure you already understand.

Arrays#

Any column can be declared as an array of its base type — useful for small, unordered collections that don't warrant a separate join table:

CREATE TABLE products (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
    ('Wireless Mouse', ARRAY['electronics', 'accessories', 'wireless']);

-- ANY: does the array contain this value?
SELECT * FROM products WHERE 'wireless' = ANY(tags);

-- @> works on arrays too: does tags contain both of these?
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'wireless'];

Arrays are convenient for small, simple tag-like lists; once the "list" needs its own attributes (a many-to-many relationship with extra columns, like an order_items join table tracking quantity and price) a proper join table is still the right tool.

UUID#

UUID stores a 128-bit universally unique identifier — useful as a primary key when ids need to be generated by the application (or multiple systems) without coordinating with a central sequence, or when you don't want ids to leak information about row count/order (an incrementing id on a public API tells competitors roughly how many signups you have).

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE api_tokens (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT NOT NULL,
    token   VARCHAR(64) NOT NULL
);

The trade-off: a UUID is larger (16 bytes vs. 4 for INT) and, being random, doesn't cluster well on insert the way a sequential integer does — this can fragment a B-tree index over time on very large, high-insert-rate tables.

Range types#

A range type stores a lower and upper bound as a single value — genuinely useful for anything modeled as "from X to Y," like a booking's date range:

CREATE TABLE bookings (
    id      SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    during  DATERANGE NOT NULL
);

INSERT INTO bookings (room_id, during) VALUES
    (101, '[2026-03-01, 2026-03-05)');   -- inclusive start, exclusive end

-- Overlap operator: does this range overlap any existing booking?
SELECT * FROM bookings WHERE room_id = 101 AND during && '[2026-03-03, 2026-03-07)';

Combined with an exclusion constraint, PostgreSQL can enforce "no two bookings for the same room may overlap" directly at the database level — a rule that's awkward to express as a simple CHECK constraint but natural with range types.

Common mistakes#

• Reaching for JSON/JSONB as a way to avoid designing a schema, then discovering the app constantly needs to filter, join, or aggregate on fields buried inside the blob — at that point, those fields should be real columns.
• Using -> where ->> is needed (or vice versa) — -> returns JSONB (useful for chaining or re-storing), ->> returns text (needed for direct string comparisons); mixing them up produces confusing "no rows match" results since payload -> 'plan' = 'pro' compares JSONB to text and never matches.
• Choosing random UUID primary keys for a very high-write-throughput table without considering the index fragmentation cost, when a sequential BIGINT (or PostgreSQL's newer UUID v7-style time-ordered generation) would insert more efficiently.
• Storing a genuinely relational many-to-many structure as an array column because it seemed simpler upfront, then hitting a wall the first time a query needs to filter or join on the array's contents efficiently at scale.