How to Use Array in PostgreSQL: Syntax and Examples

In PostgreSQL, you can use ARRAY to store multiple values in a single column as an array. Arrays can be created, queried, and manipulated using built-in functions and operators like ARRAY[], unnest(), and array indexing.

Syntax

PostgreSQL arrays are defined using the ARRAY keyword or by specifying the data type with square brackets. You can declare an array column in a table or use array literals in queries.

• Array literal: Use ARRAY[elem1, elem2, ...] to create an array.
• Array column type: Define a column as data_type[] to store arrays.
• Access elements: Use array_column[index] to get an element (1-based index).

sql

CREATE TABLE example (
  id SERIAL PRIMARY KEY,
  tags TEXT[]
);

-- Insert an array literal
INSERT INTO example (tags) VALUES (ARRAY['red', 'green', 'blue']);

-- Select first element
SELECT tags[1] FROM example WHERE id = 1;

Example

This example shows how to create a table with an array column, insert data, and query array elements and unnest arrays into rows.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  features TEXT[]
);

INSERT INTO products (name, features) VALUES
('Phone', ARRAY['Bluetooth', 'Camera', 'GPS']),
('Laptop', ARRAY['SSD', 'Touchscreen', 'Backlit Keyboard']);

-- Select all products with their features
SELECT id, name, features FROM products;

-- Get the first feature of each product
SELECT name, features[1] AS first_feature FROM products;

-- Expand features into separate rows
SELECT name, unnest(features) AS feature FROM products ORDER BY name;

Output

 id |  name  |           features           
----+--------+------------------------------
  1 | Phone  | {Bluetooth,Camera,GPS}
  2 | Laptop | {SSD,Touchscreen,Backlit Keyboard}

 name  | first_feature 
-------+---------------
 Phone | Bluetooth
 Laptop| SSD

 name  |      feature       
-------+--------------------
 Laptop| Backlit Keyboard
 Laptop| SSD
 Laptop| Touchscreen
 Phone | Bluetooth
 Phone | Camera
 Phone | GPS

Common Pitfalls

Common mistakes when using arrays in PostgreSQL include:

• Using zero-based indexing instead of PostgreSQL's 1-based indexing for arrays.
• Trying to compare arrays directly without using proper operators like @> (contains) or = ANY(array).
• Not unnesting arrays when you want to work with individual elements as rows.

Example of wrong and right usage:

sql

-- Wrong: zero-based index (will return NULL)
SELECT features[0] FROM products WHERE id = 1;

-- Right: one-based index
SELECT features[1] FROM products WHERE id = 1;

-- Wrong: direct equality check with array
SELECT * FROM products WHERE features = ARRAY['Bluetooth', 'Camera'];

-- Right: check if array contains an element
SELECT * FROM products WHERE features @> ARRAY['Camera'];

Quick Reference

Operation	Syntax	Description
Create array literal	ARRAY[elem1, elem2, ...]	Defines an array value
Declare array column	data_type[]	Defines a column to store arrays
Access element	array_column[index]	Gets element at 1-based index
Unnest array	unnest(array_column)	Expands array elements into rows
Check contains	array_column @> ARRAY['value']	Checks if array contains value
Check any element	value = ANY(array_column)	Checks if value is in array

Key Takeaways

Use ARRAY[] to create arrays and data_type[] to declare array columns.

PostgreSQL arrays use 1-based indexing, not zero-based.

Use unnest() to convert array elements into separate rows for querying.

Use operators like @> and = ANY() to check array contents.

Avoid comparing arrays directly with = unless exact match is intended.