âš¡IntermediateJSON & JSONB
Array Data Type
Store multiple values in a single column using PostgreSQL's native array type.
Overview
PostgreSQL supports native array columns any data type can have an array variant: INTEGER[], TEXT[], DATE[], even custom types. Arrays are useful for storing ordered sets of values (e.g., tags, phone numbers, ordered steps) when a normalised table would be overkill.
Array operators include @> (contains), && (overlap), and ANY/ALL for element-level comparisons. UNNEST() expands an array into rows for processing. Array access uses 1-based indexing: arr[1] for the first element. When you need to query inside arrays frequently, prefer normalised tables or JSONB with GIN indexes.
Key Points
- •Column type: tags TEXT[], scores INTEGER[] etc.
- •Access element: arr[1] (1-indexed)
- •Array literal: ARRAY[1,2,3] or '{1,2,3}'
- •@> checks if array contains an element; && checks for overlap
- •UNNEST(arr) expands an array into a set of rows
- •ARRAY_AGG(col) aggregates column values into an array
Examples
Creating and querying arrays
-- Create a table with array column
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
tags TEXT[]
);
INSERT INTO products (name, tags)
VALUES ('Widget', ARRAY['sql','postgres','tutorial']);
-- Access element (1-indexed)
SELECT tags[1] FROM products; -- 'sql'
-- Check containment
SELECT * FROM products WHERE tags @> ARRAY['postgres'];
-- Overlap: has at least one of these tags
SELECT * FROM products WHERE tags && ARRAY['sql','python'];UNNEST and ARRAY_AGG
-- Expand array to rows
SELECT id, UNNEST(tags) AS tag FROM products;
-- Aggregate column values to an array
SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS all_tags
FROM products, UNNEST(tags) AS t(tag);Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4