Array data type in PostgreSQL

Introduction

Arrays let you store multiple values in one column. This helps keep related data together.

You want to store a list of phone numbers for one person in a single row.

You need to keep multiple tags or categories for a blog post in one field.

You want to save multiple scores or ratings for a product in one place.

You want to store multiple email addresses for a contact without creating extra tables.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name data_type[],
  ...
);

-- Example:
CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT,
  grades INTEGER[]
);

The square brackets [] after the data type mean this column holds an array of that type.

You can use any data type inside the array, like INTEGER[], TEXT[], or BOOLEAN[].

Examples

This creates a table where each employee can have multiple phone numbers stored in one column.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  phone_numbers TEXT[]
);

Inserts a row with two phone numbers stored as an array.

PostgreSQL

INSERT INTO employees (name, phone_numbers) VALUES ('Alice', ARRAY['123-4567', '987-6543']);

This selects the first phone number from the array for each employee.

PostgreSQL

SELECT name, phone_numbers[1] AS first_phone FROM employees;

Shows how to insert an empty array of text type.

PostgreSQL

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

INSERT INTO empty_array_example (tags) VALUES (ARRAY[]::TEXT[]);

Sample Program

This program creates a books table with an array column for authors. It inserts two books, one with two authors and one with one author, then selects all rows.

PostgreSQL

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT,
  authors TEXT[]
);

INSERT INTO books (title, authors) VALUES
('Learn SQL', ARRAY['John Doe', 'Jane Smith']),
('Cooking 101', ARRAY['Chef Mike']);

SELECT id, title, authors FROM books;

Important Notes

Access array elements using square brackets, like column_name[1] for the first element.

Arrays start at index 1 in PostgreSQL, not 0.

Arrays can make queries simpler but can be harder to search deeply compared to normalized tables.

Summary

Arrays store multiple values in one column using square brackets.

Use arrays when you want to keep related lists together without extra tables.

Remember PostgreSQL arrays start at index 1, and you can access elements with column[index].