How Do I Declare an Array Data Type in PostgreSQL?


Angela Bailey

In PostgreSQL, declaring an array data type allows you to store multiple values of the same data type within a single column of a table. This can be extremely useful when working with large amounts of related data. In this tutorial, we will explore the different ways to declare an array data type in PostgreSQL.

Declaring an Array Data Type

There are two main ways to declare an array data type in PostgreSQL: using the square bracket notation or using the ARRAY keyword.

Square Bracket Notation

To declare an array using square bracket notation, you need to specify the data type followed by square brackets. For example, to declare an array of integers, you would use:


This will create a column that can store multiple integer values.

ARRAY Keyword

The ARRAY keyword provides a more flexible way to declare an array data type. It allows you to specify both the data type and the number of dimensions for the array. Here’s how you can use it:


  • Data Type: Specifies the data type for the elements of the array.
  • Dimension: Specifies the number of dimensions for the array. This can be any positive integer or omitted entirely.

For example, if you want to declare a one-dimensional array of text values, you would use:


If you want to declare a two-dimensional array of integers, you would use:


Using Array Data Type in Table Columns

Once you have declared an array data type, you can use it in the definition of table columns. Here’s an example:

CREATE TABLE employees (
  name TEXT,
  phone_numbers TEXT[]

In this example, the column phone_numbers is declared as an array of text values.

Inserting Values into Array Columns

To insert values into an array column, you can use the array constructor syntax or provide a comma-separated list of values within curly braces. Here are examples of both methods:

-- Using the array constructor syntax
INSERT INTO employees (name, phone_numbers)
VALUES ('John Doe', ARRAY['555-1234', '555-5678']);

-- Using a comma-separated list
INSERT INTO employees (name, phone_numbers)
VALUES ('Jane Smith', '{555-9876, 555-5432}');

Retrieving Values from Array Columns

You can retrieve values from an array column using various PostgreSQL functions and operators. Here are a few examples:

  • unnest: Unnests an array into individual rows.
  • array_length: Returns the length of an array.
  • @>: Checks if one array contains all the elements of another array.

Here’s an example of retrieving values using these functions:

-- Unnesting an array
SELECT id, unnest(phone_numbers) AS phone_number
FROM employees;

-- Retrieving the length of an arra

Discord Server - Web Server - Private Server - DNS Server - Object-Oriented Programming - Scripting - Data Types - Data Structures

Privacy Policy