Advanced Data Types: Arrays, HSTORE, UUID & Geography
Leverage PostgreSQL's powerful extended types for arrays, key-value stores, ranges, and spatial data.
🎯 What You'll Learn
- Arrays for multi-value columns with GIN indexing
- HSTORE for flat key-value pairs
- UUID vs auto-increment IDs and UUIDv7
- Range types with exclusion constraints for bookings
- PostGIS geography types for location queries
📦 Arrays
Arrays let you store multiple values in a single column — like a product having multiple tags. Think of it as a small list embedded directly in the row, queryable with special operators.
💡 Pro Tip — Arrays Are 1-Indexed
Unlike most programming languages, PostgreSQL arrays start at index 1, not 0. tags[1] gets the first element.
PostgreSQL Arrays
Store, query, and index multi-value columns
-- PostgreSQL Arrays: store multiple values in one column
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200),
tags TEXT[] NOT NULL DEFAULT '{}',
prices DECIMAL(10,2)[],
feature_flags BOOLEAN[]
);
INSERT INTO products (name, tags, prices) VALUES
('Laptop Pro', ARRAY['electronics', 'computers', 'portable'], ARRAY[999.99, 899.99, 849.99]),
('Wireless Mouse', '{electronics,accessories,wireless}', '{29.99,24.99}');
-- Query arrays:
SELECT name FROM products WHERE
...🗝️ HSTORE
HSTORE is a lightweight key-value store — simpler than JSONB but limited to flat string pairs. Good for user preferences, feature flags, and simple metadata.
HSTORE Key-Value Pairs
Flat key-value storage and migration to JSONB
-- HSTORE: key-value pairs (simpler than JSONB)
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE TABLE settings (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
preferences HSTORE DEFAULT ''
);
INSERT INTO settings (user_id, preferences) VALUES
(1, 'theme => dark, language => en, font_size => 14'),
(2, 'theme => light, language => fr, notifications => off');
-- Query hstore:
SELECT user_id,
preferences -> 'theme' AS theme,
preferences -> 'language' AS lang,
preferences ? 'n
...🆔 UUID
UUIDs are 128-bit identifiers that are globally unique without any central authority. Like snowflakes — no two are ever the same, even across different servers.
⚠️ Common Mistake — Random UUIDs Kill B-Tree Performance
Random UUIDv4 values fragment B-Tree indexes because inserts hit random leaf pages. Use time-ordered UUIDv7 for primary keys — they're sequential like auto-increment but globally unique.
UUID Identifiers
Globally unique IDs for distributed systems
-- UUID: Universally Unique Identifiers
-- 128-bit values like: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
-- PostgreSQL has built-in UUID support:
CREATE TABLE orders (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
customer_id UUID NOT NULL,
total DECIMAL(12,2),
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO orders (customer_id, total)
VALUES (gen_random_uuid(), 149.99)
RETURNING id;
-- UUID advantages:
-- ✅ Globally unique (no coordination between servers)
-- ✅ Can generate cl
...📏 Range Types
Range types represent intervals natively — date ranges, number ranges, timestamp ranges. Combined with exclusion constraints, they prevent overlapping bookings at the database level.
Range Types & Exclusion Constraints
Prevent overlapping hotel bookings with ranges
-- Range Types: represent intervals natively
-- Built-in range types:
-- int4range, int8range, numrange, tsrange, tstzrange, daterange
CREATE TABLE room_bookings (
id SERIAL PRIMARY KEY,
room_id INT NOT NULL,
guest_name VARCHAR(100),
stay daterange NOT NULL,
-- Prevent overlapping bookings for same room:
EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);
INSERT INTO room_bookings (room_id, guest_name, stay) VALUES
(101, 'Alice', '[2024-06-01, 2024-06-05)'),
(101, 'Bo
...🌍 Geography (PostGIS)
PostGIS adds geographic data types to PostgreSQL — store latitude/longitude points, polygons, and lines, then query by distance, containment, and intersection.
PostGIS Geography
Location queries: nearest, within radius
-- Geographic data types (PostGIS extension)
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(200),
category VARCHAR(50),
coordinates GEOGRAPHY(POINT, 4326) -- lat/lng on Earth
);
INSERT INTO locations (name, category, coordinates) VALUES
('Central Park', 'park', ST_MakePoint(-73.9654, 40.7829)::geography),
('Times Square', 'landmark', ST_MakePoint(-73.9855, 40.7580)::geography),
('Empire State', 'building', ST_MakePoint(-73.
...📋 Quick Reference
| Type | Use Case | Index |
|---|---|---|
| TEXT[] | Tags, categories | GIN |
| HSTORE | Simple key-value | GIN/GiST |
| UUID | Distributed IDs | B-Tree |
| daterange | Bookings, periods | GiST |
| GEOGRAPHY | Lat/lng queries | GiST |
🎉 Lesson Complete!
You now know PostgreSQL's most powerful extended types. Next, learn about database security, roles, and row-level security!
Sign up for free to track which lessons you've completed and get learning reminders.