Advanced Data Types

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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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.

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 .

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.

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

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 || .

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.

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.

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.

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.

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.

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:

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.

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.

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary