Array Data Type in PostgreSQL

PostgreSQL allows columns to store multiple values of the same type in a single array. Every data type has a companion array type — integer[], text[], boolean[], and so on. Arrays are useful for storing multi-valued attributes like product tags, image URLs, or notification channels without requiring a separate join table.

Syntax

column_name datatype[]       -- one-dimensional array
column_name datatype[][]     -- two-dimensional array

Array subscripts in PostgreSQL are 1-based — the first element is at index 1.

Practical Example

Store product tags and available sizes in array columns:

CREATE TABLE products (
  id           SERIAL PRIMARY KEY,
  sku          VARCHAR(50)  NOT NULL UNIQUE,
  name         VARCHAR(200) NOT NULL,
  tags         TEXT[]       NOT NULL DEFAULT '{}',
  sizes        VARCHAR(10)[]
);

-- Using the ARRAY constructor
INSERT INTO products (sku, name, tags, sizes)
VALUES
  ('BOLT-TI-M6', 'Titanium M6 Bolt',
   ARRAY['fastener', 'titanium', 'metric'],
   ARRAY['M6x10', 'M6x16', 'M6x20', 'M6x25']),
  ('HOODIE-ECO', 'Eco Merino Hoodie',
   ARRAY['apparel', 'merino', 'eco'],
   ARRAY['XS', 'S', 'M', 'L', 'XL']),
  ('LAMP-SOLAR', 'Solar Desk Lamp',
   ARRAY['lighting', 'solar', 'eco'],
   NULL);

Using curly-brace literal syntax as an alternative:

INSERT INTO products (sku, name, tags)
VALUES ('NOTEBOOK-A5', 'Recycled Notebook', '{"stationery","recycled","eco"}');

Accessing Array Elements

-- First element (1-based index)
SELECT sku, tags[1] AS primary_tag FROM products;

-- Second element
SELECT sku, sizes[2] AS second_size FROM products WHERE sizes IS NOT NULL;

Searching Arrays

-- ANY(): check if a value exists anywhere in the array
SELECT sku, name
FROM products
WHERE 'eco' = ANY(tags);

-- @>: containment — does the array contain ALL listed values?
SELECT sku, name
FROM products
WHERE tags @> ARRAY['eco', 'apparel'];

-- &&: overlap — does the array contain ANY of the listed values?
SELECT sku, name
FROM products
WHERE tags && ARRAY['titanium', 'solar'];

Updating Array Data

-- Update a single element
UPDATE products
SET sizes[1] = 'M6x12'
WHERE sku = 'BOLT-TI-M6'
RETURNING sku, sizes;

-- Append an element with array_append()
UPDATE products
SET tags = array_append(tags, 'sale')
WHERE sku = 'HOODIE-ECO';

-- Remove an element with array_remove()
UPDATE products
SET tags = array_remove(tags, 'sale')
WHERE sku = 'HOODIE-ECO';

Expanding Arrays to Rows

-- unnest() returns one row per array element
SELECT sku, unnest(tags) AS tag
FROM products
ORDER BY sku, tag;

-- WITH ORDINALITY adds the position of each element
SELECT sku, tag, pos
FROM products,
     unnest(tags) WITH ORDINALITY AS t(tag, pos)
WHERE sizes IS NOT NULL
ORDER BY sku, pos;

GIN Indexing

Create a GIN index on the array column to speed up @> and && containment queries:

CREATE INDEX CONCURRENTLY idx_products_tags
ON products USING GIN (tags);

EXPLAIN ANALYZE
SELECT sku FROM products WHERE tags @> ARRAY['eco'];

Testing with Vela

Array columns are easy to get right in development but can produce slow sequential scans in production when the GIN index is missing or stale. Before deploying a feature that queries by array values, test the exact query plan on a database branch loaded with production-scale data to confirm the GIN index is being used and the query latency is acceptable.

Production Tips

• Array subscripts are 1-based in PostgreSQL — the first element is arr[1], not arr[0].
• For large tables that need to search inside arrays, create a GIN index with CREATE INDEX CONCURRENTLY to avoid downtime.
• Avoid deeply nesting arrays or storing very large arrays in a single column — consider a child table with a foreign key for unbounded one-to-many relationships.
• Use unnest() with WITH ORDINALITY when you need the position of each element alongside the value.
• Use array_length(col, 1) to get the count of elements in a one-dimensional array, not length() which is for strings.