Advanced Data Types

Reviewed & published by Brayan K

PostgreSQL goes far beyond text and numbers. By the end of this lesson you'll store lists, key-value pairs, globally-unique ids, fixed label sets, and intervals natively — and query them with the special operators (ANY, @>, &&, ->) that make these types worth using.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Store lists in one column with ARRAY types
• Test membership with ANY, @> and overlap with &&
• Expand arrays into rows with unnest()
• Keep flat key-value data in an HSTORE column
• Generate globally-unique ids with UUID + gen_random_uuid()
• Constrain values with ENUM and model intervals with range types (&&)

Our Sample Table: articles

The array, unnest, and challenge examples all run against this small articles table. The tags column is a TEXT[] — an array of text inside a single cell.

1. ARRAY Columns — Many Values in One Cell

An array column stores a list of values inside a single cell. Add [] to any type — int[], TEXT[], BOOLEAN[] — and that column now holds a list instead of one value. It's perfect for tags, role names, or any short list that "belongs" to a row.

A blog post's tags are like the stickers on a parcel — several labels stuck to one box. An array column lets you keep all those labels on the row itself, instead of building a whole separate "tags" table.

-- An array column stores MANY values in one cell.
-- Add [] to any type to make it an array of that type.
CREATE TABLE articles (
    id    SERIAL PRIMARY KEY,
    title TEXT,
    tags  TEXT[] NOT NULL DEFAULT '{}'   -- a text array; default = empty {}
);

INSERT INTO articles (title, tags) VALUES
  ('Intro to SQL',     ARRAY['sql','beginner']),          -- ARRAY[...] literal
  ('Postgres Arrays',  '{sql,postgres,advanced}'),        -- '{...}' literal
  ('Cooking Pasta',    ARRAY['food','italian','dinner']),
  ('Untagged Draft',   '{}');                             -- empty array (NOT null)

-- The whole tags value lives in a single column:
SELECT title, tags FROM articles;

-- ✅ Expected result:
-- title | tags
-- Intro to SQL | {sql,beginner}
-- Postgres Arrays | {sql,postgres,advanced}
-- Cooking Pasta | {food,italian,dinner}
-- Untagged Draft | {}

The real power is in querying arrays. The two questions you'll ask most are "is this value in the array?" and "do these two arrays share anything?" — answered by ANY/@> and && respectively.

Unlike most programming languages, PostgreSQL arrays start at index 1, not 0. So tags[1] is the first element, and tags[0] just returns NULL.

-- Read an element by POSITION (Postgres arrays start at 1, not 0!):
SELECT title, tags[1] AS first_tag FROM articles;

-- "Is this value anywhere in the array?" — two equivalent ways:
SELECT title FROM articles WHERE 'sql' = ANY(tags);     -- ANY: value vs each element
SELECT title FROM articles WHERE tags @> ARRAY['sql'];  -- @> : array CONTAINS array

-- "Do the arrays share ANY value?" — the && overlap operator:
SELECT title FROM articles WHERE tags && ARRAY['food','sql'];

-- Handy array functions:
SELECT title,
       array_length(tags, 1)      AS tag_count,   -- length along dimension 1
       array_to_string(tags, ', ') AS tag_list     -- join into one string
FROM articles;

-- ✅ Expected result:
-- title | tag_count | tag_list
-- Intro to SQL | 2 | sql, beginner
-- Postgres Arrays | 3 | sql, postgres, advanced
-- Cooking Pasta | 3 | food, italian, dinner
-- Untagged Draft | NULL |

unnest() turns an array into rows — one row per element. This is the bridge back to "normal" SQL: once values are rows, you can GROUP BY, JOIN, or count them like any other column.

-- unnest() explodes an array into ONE ROW PER ELEMENT.
-- Useful for grouping, counting, or joining on individual values.
SELECT title, unnest(tags) AS tag
FROM articles;
-- Intro to SQL    | sql
-- Intro to SQL    | beginner
-- Postgres Arrays | sql
-- Postgres Arrays | postgres ...

-- Combine with GROUP BY to count how often each tag is used:
SELECT tag, COUNT(*) AS uses
FROM articles, unnest(tags) AS tag
GROUP BY tag
ORDER BY uses DESC, tag;

-- ✅ Expected result:
-- tag | uses
-- sql | 2
-- advanced | 1
-- beginner | 1
-- dinner | 1
-- food | 1
-- italian | 1
-- postgres | 1

Your Turn: rows where the array contains a value

Fill in the blank so the query returns every article tagged 'sql'. The expected result is in the comments so you can check yourself.

-- 🎯 YOUR TURN — fill in the blank, then press "Try it Yourself"
-- Goal: list every article that is tagged 'sql'.

SELECT title
FROM articles
WHERE 'sql' = ___(tags);   -- 👉 the operator that tests value-vs-each-element

-- ✅ Expected result (2 rows): Intro to SQL, Postgres Arrays
-- (Bonus: the same result comes from  WHERE tags @> ARRAY['sql'])

2. HSTORE — Flat Key-Value Pairs

HSTORE stores a set of key => value string pairs in one column — think of it as a tiny dictionary per row. It's great for sparse, optional settings (a theme here, a language there) where adding a real column for each would be wasteful.

HSTORE is an extension, so you enable it once per database with CREATE EXTENSION IF NOT EXISTS hstore;. Read a value with ->, test for a key with ?, and merge/update keys with ||.

-- HSTORE stores flat KEY => VALUE string pairs in one column.
-- It is a Postgres extension, so enable it once per database:
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE profiles (
    user_id     INT PRIMARY KEY,
    preferences HSTORE          -- e.g. theme, language, font_size
);

INSERT INTO profiles (user_id, preferences) VALUES
  (1, 'theme => dark,  language => en'),
  (2, 'theme => light, language => fr, beta => on');

-- ->  reads the value for a key (NULL if the key is missing):
SELECT user_id,
       preferences -> 'theme'    AS theme,
       preferences -> 'language' AS lang
FROM profiles;

-- ?  tests whether a key EXISTS:
SELECT user_id FROM profiles WHERE preferences ? 'beta';   -- only user 2

-- ||  merges/updates keys; this adds or overwrites font_size:
UPDATE profiles
SET preferences = preferences || 'font_size => 16'
WHERE user_id = 1;

-- ✅ Expected result:
-- user_id
-- 2

3. UUID — Globally Unique Identifiers

A UUID is a 128-bit identifier such as a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. The point is that it's unique everywhere, with no central counter — two different servers can each mint ids that will never collide. Set a column's DEFAULT to gen_random_uuid() and Postgres fills in a fresh value on every insert.

Sequential SERIAL ids leak information (id 1042 tells the world you have ~1042 rows) and require the database to hand out the next number. UUIDs are unguessable and can be generated in your app before the row even exists.

-- A UUID is a 128-bit id like a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11.
-- gen_random_uuid() (built in on modern Postgres) makes a fresh one.
CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- auto-filled id
    customer    TEXT,
    total       NUMERIC(10,2),
    created_at  TIMESTAMP DEFAULT now()
);

-- You don't supply id — the DEFAULT generates it for you:
INSERT INTO orders (customer, total) VALUES ('Ada', 49.99)
RETURNING id;   -- returns the new UUID, e.g. 3f2a...

-- Why UUIDs instead of SERIAL/auto-increment?
--   + Globally unique: no two servers ever collide, no central counter.
--   + Generatable in app code before the row is even inserted.
--   + Not guessable in URLs (sequential ids 1,2,3 leak how many rows exist).
--   - 16 bytes vs 4 for an INT, and random UUIDv4 hurts B-Tree index locality.
-- Tip: UUIDv7 (time-ordered) keeps uniqueness but inserts in order — best of both.

⚠️ Random UUIDs can hurt index performance

Random UUIDv4 values scatter across the B-Tree index, so inserts touch random pages and slow down on huge tables. Time-ordered UUIDv7 (Postgres 18 has uuidv7() built in) inserts in order like SERIAL while staying globally unique — the best default for new primary keys.

4. ENUM — A Fixed Set of Labels

An ENUM is a custom type whose value must be one of a fixed list of labels — like 'pending', 'shipped', 'delivered'. It both documents the allowed values and rejects anything else at write time, so a typo like 'shiped' errors instead of quietly saving bad data.

A nice bonus: ENUM values sort in the order you defined them, not alphabetically — so ORDER BY status naturally goes pending → shipped → delivered.

-- An ENUM is a custom type limited to a fixed list of labels.
-- It documents the allowed values AND enforces them.
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');

CREATE TABLE shipments (
    id     SERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO shipments (status) VALUES ('pending'), ('shipped'), ('delivered');

-- ENUMs compare and SORT in their DEFINITION order, not alphabetically:
SELECT status FROM shipments ORDER BY status;   -- pending, shipped, delivered

-- This is rejected at write time — a safety net against typos/bad data:
-- INSERT INTO shipments (status) VALUES ('shiped');
-- ERROR: invalid input value for enum order_status: "shiped"

-- Adding a value is easy; REMOVING or REORDERING one is not (see Common Errors):
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

-- ✅ Expected result:
-- status
-- pending
-- shipped
-- delivered

5. Range Types — Intervals as One Value

A range type stores an interval — a low and a high bound — in a single value. Built-ins include int4range, numrange, daterange, and tsrange. The bracket style sets inclusivity: [ includes the bound, ) excludes it, so '[2024-06-01,2024-06-05)' covers June 1–4.

The killer operator is && (overlap): "do these two ranges intersect?" That single check answers "is this room already booked for those dates?" — the heart of every scheduling and booking system.

-- A range type stores an interval (a low and high bound) in one value.
-- Built-ins include int4range, numrange, daterange, tsrange.
-- Brackets set inclusivity:  [ = inclusive,  ) = exclusive.
SELECT
  int4range(1, 10)             AS ints,    -- 1..9  (10 excluded)
  '[2024-06-01,2024-06-05)'::daterange AS stay;   -- Jun 1..4

-- A booking table where each stay is a daterange:
CREATE TABLE bookings (
    id    SERIAL PRIMARY KEY,
    room  INT,
    stay  daterange
);
INSERT INTO bookings (room, stay) VALUES
  (101, '[2024-06-01,2024-06-05)'),
  (101, '[2024-06-05,2024-06-10)'),
  (102, '[2024-06-03,2024-06-08)');

-- @>  : does the range CONTAIN a value?
SELECT room FROM bookings WHERE stay @> '2024-06-03'::date;   -- room 101 & 102

-- &&  : do two ranges OVERLAP? (the key check for double-booking)
SELECT room, stay FROM bookings
WHERE stay && '[2024-06-04,2024-06-06)'::daterange;          -- both room-101 stays

-- lower()/upper() pull the bounds back out:
SELECT room, lower(stay) AS check_in, upper(stay) AS check_out FROM bookings;

-- ✅ Expected result:
-- room | check_in | check_out
-- 101 | 2024-06-01 | 2024-06-05
-- 101 | 2024-06-05 | 2024-06-10
-- 102 | 2024-06-03 | 2024-06-08

Your Turn: UUID default + range overlap

Two blanks: give the table a self-filling UUID id, then find the slots that overlap a given range. The expected result is in the comments.

-- 🎯 YOUR TURN — fill in the two blanks, then press "Try it Yourself"
-- Part A: give the table a UUID id that fills itself in automatically.
CREATE TABLE tickets (
    id    UUID PRIMARY KEY DEFAULT ___(),   -- 👉 the random-UUID function
    slot  daterange
);
INSERT INTO tickets (slot) VALUES
  ('[2024-07-01,2024-07-03)'),
  ('[2024-07-05,2024-07-08)');

-- Part B: find slots that clash with Jul 2 – Jul 6.
SELECT slot FROM tickets
WHERE slot ___ '[2024-07-02,2024-07-06)'::daterange;   -- 👉 the OVERLAP operator

-- ✅ Expected: a fresh UUID auto-generated for each row, and Part B returns
--    BOTH slots ([07-01,07-03) overlaps the 2nd; [07-05,07-08) overlaps the 6th).

A quick word on Geography (PostGIS)

For maps and location data there's the PostGIS extension, which adds GEOGRAPHY and GEOMETRY types for points, lines, and polygons on the Earth's surface. After CREATE EXTENSION postgis; you can store a coordinate as GEOGRAPHY(POINT, 4326) and ask spatial questions:

• ST_DWithin(a, b, 2000) — is point a within 2,000 metres of b? (great for "near me")
• ST_Distance(a, b) — how far apart, in metres.
• A GiST index plus the <-> distance operator powers fast "nearest 5" queries.

PostGIS is a deep topic of its own — just know it exists so you never store lat/lng as two plain numbers and reinvent distance maths by hand.

Common Errors (and the fix)

• Array off-by-one: reaching for tags[0] returns NULL, not the first element. PostgreSQL arrays are 1-indexed — use tags[1].
• NULL hiding in an array: 'x' = ANY(ARRAY['a', NULL]) is not false — a NULL element makes the result NULL (treated as not-true), so rows can silently vanish. Keep arrays NULL-free, or filter with array_remove(tags, NULL).
• "function gen_random_uuid() does not exist": on older Postgres, enable it with CREATE EXTENSION IF NOT EXISTS pgcrypto; (it's built in from Postgres 13+).
• UUID vs SERIAL trade-off: don't default to UUID for everything — they're 4× the storage and random v4 hurts index locality. Use SERIAL/IDENTITY for internal tables; reach for UUID (ideally v7) when ids cross systems or appear in URLs.
• ENUM migration pain: you can ALTER TYPE ... ADD VALUE, but you cannot easily remove or reorder a label — that needs recreating the type and rewriting the column. Plan the label list up front, or use a lookup table + foreign key when it'll change often.
• "operator does not exist: text[] @> text": @> compares an array to an array. Write tags @> ARRAY['sql'], not tags @> 'sql'.

Frequently Asked Questions

Q: Should I use an array column or a separate table?

Use an array for a short, simple list that's read together and rarely queried on its own (e.g. tags). Use a separate "child" table when the items need their own columns, constraints, or heavy querying. Arrays trade relational flexibility for convenience.

Q: HSTORE or JSONB for settings?

Prefer JSONB for almost everything new — it supports nesting, arrays, numbers, and richer operators. Choose HSTORE only when your data is genuinely flat string-to-string and you want the absolute simplest key-value store.

Q: Are UUIDs always better than auto-increment ids?

No. UUIDs win when ids must be unique across systems, generated client-side, or exposed in URLs. For a single internal database, SERIAL/IDENTITY is smaller and faster. If you do want UUIDs, prefer time-ordered UUIDv7 to keep index performance.

Q: Why does tags[0] give me NULL?

Because PostgreSQL arrays start at index 1, not 0. The first element is tags[1]; index 0 is simply out of range and returns NULL.

Q: Do these types work in MySQL or SQLite?

Mostly no — arrays, HSTORE, ENUM types, ranges and PostGIS are Postgres features. MySQL has its own ENUM and JSON columns; SQLite has neither. If portability matters, model lists/settings with JSON or join tables instead.

Mini-Challenge: Tag Detective

Put it together — a brief, a blank canvas, and the expected result in the comments. Write it, then run it in a Postgres playground to confirm.

-- 🎯 MINI-CHALLENGE
-- Using ONLY this lesson's ideas (arrays, ANY/@>, unnest, ranges/&&):
--   1. From the 'articles' table, find every article tagged 'sql'
--      AND tagged 'beginner' at the same time.
--      (Hint: @> can take an array with MORE than one value — tags @> ARRAY[...].)
--   2. Then, separately, list each distinct tag with how many articles use it,
--      most-used first.
--
-- ✅ Expected #1 (1 row): Intro to SQL
-- ✅ Expected #2: sql | 2 , then the rest with 1 each

-- your queries here

🎉 Lesson Complete

• ✅ type[] array columns store lists; query with ANY, @>, && and explode with unnest()
• ✅ HSTORE holds flat key-value pairs (->, ?, ||) — but JSONB is the modern default
• ✅ UUID + gen_random_uuid() gives globally-unique ids; prefer UUIDv7 for primary keys
• ✅ ENUM constrains a column to fixed labels that sort in definition order
• ✅ Range types model intervals; && detects overlaps (and exclusion constraints stop double-bookings)
• ✅ Next: Database Security — roles, privileges, and row-level security

Practice quiz

Continue this course

• Previous: JSON & Semi-Structured Data Handling in SQL
• Next: Database Security: Roles, Privileges, Policies & Row-Level Security — Implement RBAC, column-level privileges, and row-level security policies
• Quick reference: SQL cheat sheet

Related lessons

• SELECT