PostgreSQL Array Type: What It Is and How to Use It

In PostgreSQL, an array type allows you to store multiple values of the same data type in a single column. It works like a list or collection inside one field, making it easy to group related data together without creating separate tables.

How It Works

Think of the PostgreSQL array type as a container that holds multiple items of the same kind, similar to a shopping list inside one notebook page. Instead of creating many separate columns or rows for related values, you keep them all together in one place.

When you define a column as an array, PostgreSQL stores the values in a sequence. You can access, update, or search these values using special syntax. This makes it easier to manage grouped data without complex joins or extra tables.

Example

This example shows how to create a table with an integer array column, insert data, and query it.

sql

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT,
  scores INTEGER[]
);

INSERT INTO students (name, scores) VALUES
('Alice', ARRAY[85, 90, 78]),
('Bob', ARRAY[70, 88, 92]);

SELECT name, scores FROM students;

-- Access first score of Alice
SELECT name, scores[1] AS first_score FROM students WHERE name = 'Alice';

Output

 name  |    scores    
-------+--------------
 Alice | {85,90,78}
 Bob   | {70,88,92}
(2 rows)

 name  | first_score 
-------+-------------
 Alice |          85
(1 row)

When to Use

Use PostgreSQL arrays when you want to store multiple related values in one column without creating extra tables. For example, you might store a list of phone numbers, tags, or scores for a user.

This is helpful when the number of items is small and fixed or when you want quick access without joins. However, for complex relationships or large lists, separate tables might be better.

Key Points

• Arrays store multiple values of the same type in one column.
• You can access array elements using square brackets, like array[1].
• Arrays simplify data grouping but are best for small, fixed-size lists.
• For complex data, consider normalized tables instead.

Key Takeaways

PostgreSQL arrays let you store multiple values in a single column of the same type.

You can easily access and manipulate array elements using index notation.

Arrays are great for small lists but not ideal for complex or large datasets.

Use arrays to simplify your schema when related data fits naturally in one field.