Arrays & Composite Types

JSON arrays are flexible documents; PostgreSQL arrays are stricter: one SQL value holding a typed list of SQL values. They are useful, but they are not a shortcut around data modeling. This lesson shows how to read array columns, turn arrays back into rows, aggregate rows into arrays or strings, and recognize the rare case where an array is the honest design.

Typed lists in one column

Here is a small cafe preferences table. favorite_tags is a text[], weekly_scores is an int[], and usual_drink uses a named composite type: one value with two fields.

CREATE TYPE drink_preference AS (
  drink text,
  milk text
);
 
CREATE TABLE cafe_preferences (
  customer_id int PRIMARY KEY,
  customer_name text NOT NULL,
  city text NOT NULL,
  favorite_tags text[] NOT NULL,
  weekly_scores int[] NOT NULL,
  usual_drink drink_preference NOT NULL
);
 
INSERT INTO cafe_preferences VALUES
  (1, 'Ada', 'Berlin', ARRAY['espresso', 'oat', 'quiet'], ARRAY[5, 4, 5], ROW('flat_white', 'oat')::drink_preference),
  (2, 'Linus', 'Austin', ARRAY['filter', 'whole'], ARRAY[3, 4], ROW('cappuccino', 'whole')::drink_preference),
  (3, 'Grace', 'Lisbon', ARRAY['cold_brew', 'mobile', 'oat'], ARRAY[4, 4, 5], ROW('cold_brew', 'none')::drink_preference),
  (4, 'Mina', 'Berlin', ARRAY['pastry', 'decaf'], ARRAY[2, 3, 3], ROW('decaf_latte', 'almond')::drink_preference);

CREATE TYPE
CREATE TABLE
INSERT 0 4

ARRAY[...] is the clearest literal syntax when you write values. PostgreSQL renders arrays in grids with curly-brace literals, so a text[] comes back looking like {espresso,oat,quiet}. Array positions in PostgreSQL start at 1, not 0:

SELECT p.customer_name,
       p.favorite_tags,
       p.favorite_tags[1] AS first_tag,
       p.weekly_scores[2] AS second_score,
       (p.usual_drink).drink AS usual,
       (p.usual_drink).milk AS milk
FROM cafe_preferences AS p
ORDER BY p.customer_id;

 customer_name |     favorite_tags      | first_tag | second_score |    usual    |  milk
---------------+------------------------+-----------+--------------+-------------+--------
 Ada           | {espresso,oat,quiet}   | espresso  |            4 | flat_white  | oat
 Linus         | {filter,whole}         | filter    |            4 | cappuccino  | whole
 Grace         | {cold_brew,mobile,oat} | cold_brew |            4 | cold_brew   | none
 Mina          | {pastry,decaf}         | pastry    |            3 | decaf_latte | almond
(4 rows)

Notice the parentheses around (p.usual_drink).drink. Composite fields need that because p.usual_drink is one column value, and then .drink reaches inside it.

Search and expand arrays

To ask whether one value appears inside an array, put the value on the left and ANY(array_column) on the right:

SELECT p.customer_name,
       p.city
FROM cafe_preferences AS p
WHERE 'oat' = ANY(p.favorite_tags)
ORDER BY p.customer_name;

 customer_name |  city
---------------+--------
 Ada           | Berlin
 Grace         | Lisbon
(2 rows)

That reads as "the text 'oat' equals at least one element of this customer's tags." If the next step needs one row per tag, expand the array with unnest:

SELECT p.customer_name,
       tag.tag
FROM cafe_preferences AS p
CROSS JOIN LATERAL unnest(p.favorite_tags) AS tag(tag)
ORDER BY p.customer_name, tag.tag;

 customer_name |    tag
---------------+-----------
 Ada           | espresso
 Ada           | oat
 Ada           | quiet
 Grace         | cold_brew
 Grace         | mobile
 Grace         | oat
 Linus         | filter
 Linus         | whole
 Mina          | decaf
 Mina          | pastry
(10 rows)

This is the same move you used with jsonb_array_elements in 15.1, but now the values are ordinary SQL text instead of JSONB. LATERAL lets the function read the current customer's array.

Build arrays and display strings

Arrays are not only stored; they can be query results. ARRAY_AGG collects rows into an array, while STRING_AGG turns rows into one display string. Put ORDER BY inside the aggregate when order matters:

SELECT p.city,
       ARRAY_AGG(p.customer_name ORDER BY p.customer_name) AS customers,
       STRING_AGG(p.customer_name, ', ' ORDER BY p.customer_name) AS customer_list
FROM cafe_preferences AS p
GROUP BY p.city
ORDER BY p.city;

  city  | customers  | customer_list
--------+------------+---------------
 Austin | {Linus}    | Linus
 Berlin | {Ada,Mina} | Ada, Mina
 Lisbon | {Grace}    | Grace
(3 rows)

The two outputs serve different callers. ARRAY_AGG is still structured data: downstream SQL can unnest it again. STRING_AGG is presentation: excellent for a label or export column, but much harder to query safely later.

Composite types and row values

A composite type is a named row shape that can live in a column or be built in a query. A row value is the general idea: several values bundled into one tuple-like expression. You have already seen row values in INSERT; ROW('flat_white', 'oat')::drink_preference makes one drink_preference.

Row values can also compare multiple facts together. This query keeps customers whose (city, milk) pair appears in the small list, then returns a freshly built composite value:

SELECT p.customer_name,
       ROW((p.usual_drink).drink, (p.usual_drink).milk)::drink_preference AS usual_pair
FROM cafe_preferences AS p
WHERE (p.city, (p.usual_drink).milk) IN (
  VALUES ('Berlin', 'oat'),
         ('Lisbon', 'none')
)
ORDER BY p.customer_name;

 customer_name |    usual_pair
---------------+------------------
 Ada           | (flat_white,oat)
 Grace         | (cold_brew,none)
(2 rows)

Keep composite columns approachable in your own designs. They are handy for small value objects that travel together, but ordinary columns are usually clearer when you filter, join, constrain, or report on each field separately.

Arrays versus junction tables

An array is fine when the list is small, typed, atomic or ordered, and usually read as a unit: display tags, a short ordered survey answer, a compact set of scoring snapshots, or ingredients printed on a menu card. The array beats a junction table when splitting it would add ceremony without adding protection.

The default is still a junction table. If each element needs a foreign key, a name in a lookup table, a created-at timestamp, a permission rule, a price, or a life of its own, it wants rows. Arrays are better than comma-separated strings because they are typed and queryable, but they do not make a many-to-many relationship magically normalized. When the element becomes an entity, give it a table.

Next lesson takes the same "many tokens in one value" instinct into search: PostgreSQL full-text search turns words into indexed lexemes so queries can ask for meaning, not just exact substrings.