Arrays: Native Multi-Valued Columns

Store and query multiple values in a single column — no junction table required for simple membership tests

The beer_db stores hops and malts as text[] columns directly on the beers table. Each beer was brewed with 2–4 hops and 2–5 malts — information that matters for search and filtering but that you would never use as the left side of a JOIN.\n\nPostgreSQL's native array type supports containment queries ("does this array include Citra?"), length functions, and unnesting into rows. Paired with a GIN index, array containment queries are as fast as JSONB containment queries.\n\nThe trade-off: arrays cannot hold foreign keys, NOT NULL per element, or CHECK constraints per element. If the elements are entities you need to query independently, normalise them into a junction table. If they are attribute bags like ingredient lists, arrays are simpler and faster.

Array Literals and Column Type

-- Array literal
SELECT ARRAY['Citra', 'Mosaic', 'Saaz'] AS hops;

-- See the type of the column
\d beers   -- hops text[], malts text[]

Testing Membership: = ANY(array)

-- Beers that contain Citra anywhere in their hop array
SELECT name, hops
FROM beers
WHERE 'Citra' = ANY(hops)
LIMIT 5;

Containment: @>

@> checks whether the left array contains every element of the right array. Use this (not ANY) when you want GIN index support.

-- GIN index on hops
CREATE INDEX idx_beers_hops ON beers USING gin (hops);

-- Beers that contain both Citra AND Mosaic
SELECT name, hops
FROM beers
WHERE hops @> ARRAY['Citra', 'Mosaic'];

Array Functions

SELECT
    name,
    array_length(hops, 1)          AS hop_count,
    array_to_string(hops, ', ')    AS hops_csv
FROM beers
LIMIT 5;

unnest: Expand Array into Rows

-- One row per hop across all beers
SELECT name, unnest(hops) AS hop
FROM beers
LIMIT 10;

-- Count how many beers use each hop variety
SELECT hop, COUNT(*) AS beer_count
FROM beers, unnest(hops) AS hop
GROUP BY hop
ORDER BY beer_count DESC
LIMIT 10;

text[] (array column)

A column that holds an ordered list of values of a given type. PostgreSQL supports arrays of any base type: text[], int[], uuid[], etc. Elements are 1-indexed (array[1] is the first element). Arrays support GIN indexing for containment and equality queries.

ANY vs @> containment

value = ANY(col) tests whether a single value appears anywhere in the array. col @> ARRAY['a','b'] tests whether the column's array contains every element of the right-hand array. Containment uses the GIN index; ANY() does not — but ANY() is often clearer for single-value tests.

unnest

A set-returning function that expands an array into one row per element. Useful for aggregating across array elements — for example, counting which hop varieties appear most often across all beers. The result can be used in FROM, joined, grouped, and filtered like any regular row source.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. Array exercises use the beers table with its hops and malts text[] columns.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

📋 Inspect the Array Columns

Select name, hops, malts from beers to see how arrays are displayed. Also use array_length and array_to_string on the hops column.

SELECT name, hops, array_length(hops, 1) AS hop_count, array_to_string(hops, ', ') AS hops_csv FROM beers LIMIT 5;

name | hops | hop_count | hops_csv -----------------------+-------------------------+-----------+------------------------- Golden Lager | {Saaz,Hallertau,Fuggle} | 3 | Saaz, Hallertau, Fuggle Dark Ale | {Citra,Galaxy} | 2 | Citra, Galaxy Hoppy Stout | {Cascade,Centennial} | 2 | Cascade, Centennial Roasty Porter | {Saaz,Fuggle,Hallertau} | 3 | Saaz, Fuggle, Hallertau Crisp IPA | {Mosaic,Citra,Saaz} | 3 | Mosaic, Citra, Saaz (5 rows)

🎯 Filter by Hop Variety: = ANY()

Find all beers that include Saaz in their hops array. Use the 'value = ANY(col)' syntax.

SELECT name, style, hops FROM beers WHERE 'Saaz' = ANY(hops) ORDER BY name LIMIT 10;

name | style | hops -----------------------+------------------------+--------------------------- Anniversary Ale | Berliner Weisse | {Saaz,Hallertau} Anniversary Bitter | Czech Premium Pale Lager| {Saaz,Fuggle} Anniversary Gose | Czech Premium Pale Lager| {Saaz,Galaxy,Mosaic} Anniversary IPA | Session IPA | {Saaz,Citra,Hallertau} Anniversary Lager | American Wheat Beer | {Saaz,Centennial} ... (10 rows)

📦 Array Containment: @>

Find beers that use both Citra AND Mosaic hops. Use the @> containment operator — this is GIN-index-friendly and checks that all listed elements are present.

SELECT name, style, hops FROM beers WHERE hops @> ARRAY['Citra', 'Mosaic'] ORDER BY name;

name | style | hops -----------------------+--------------+--------------------------- Crisp IPA | Session IPA | {Mosaic,Citra,Saaz} Hazy Bitter | Double IPA | {Citra,Mosaic} Imperial Weiss | American IPA | {Mosaic,Citra,Hallertau} Session Stout | American IPA | {Citra,Mosaic,Centennial} ... (rows vary)

🔢 Unnest: Which Hops Are Used Most?

Use unnest(hops) to expand the hops array into one row per hop, then GROUP BY and COUNT to find the 10 most commonly used hop varieties across all beers.

SELECT hop, COUNT(*) AS beer_count FROM beers, unnest(hops) AS hop GROUP BY hop ORDER BY beer_count DESC LIMIT 10;

Lab complete! You can now work with PostgreSQL array columns:\n\n\n 'val' = ANY(col) : ✅ membership test\n col @> ARRAY['a','b'] : ✅ containment (GIN-indexed)\n array_length(col, 1) : ✅ count elements\n array_to_string(col,',') : ✅ join for display\n unnest(col) : ✅ expand into rows for aggregation\n

Enable JavaScript to run the live terminal and track your progress.