What Is Array Data Type in SQL?


Angela Bailey

The array data type in SQL is a powerful tool that allows you to store and manipulate multiple values within a single column of a database table. With arrays, you can efficiently manage and access related data, making your SQL queries more concise and flexible.

Defining Array Data Type

In SQL, an array is a collection of elements of the same data type. It is declared by specifying the data type followed by square brackets []. For example, to create an array of integers:

CREATE TABLE employees(
    id INT,
    name VARCHAR(50),
    skills INT[]

In the above example, the ‘skills’ column is defined as an array of integers. This means that each row in the ’employees’ table can have multiple skill values associated with it.

Inserting Values into an Array

To insert values into an array column, you can use the ARRAY constructor function or simply provide a comma-separated list within curly braces {}.

INSERT INTO employees (id, name, skills)
VALUES (1, 'John Doe', ARRAY[1, 3, 5]);

The above query inserts three skill values (1, 3, 5) into the ‘skills’ column for John Doe.

Accessing Array Elements

You can access individual elements of an array using their index. In SQL arrays, the index starts from 1.

SELECT skills[1]
FROM employees
WHERE id = 1;

This query returns the first element in the ‘skills’ array for the employee with id=1.

Modifying Array Elements

You can modify array elements using the array assignment operator (=) with the index of the element you want to change.

UPDATE employees
SET skills[2] = 4
WHERE id = 1;

The above query updates the second element of the ‘skills’ array to 4 for the employee with id=1.

Array Functions and Operators

SQL provides a range of functions and operators that you can use to manipulate arrays:

  • array_length(array_expression, dimension): Returns the length of an array in a specified dimension.
  • array_append(array_expression, value): Adds an element to the end of an array.
  • array_remove(array_expression, value): Removes all occurrences of a specific value from an array.
  • array_agg(expression): Aggregates multiple rows into a single array value.


The array data type in SQL provides a convenient way to store and manipulate collections of related data. By leveraging arrays, you can simplify your database schema and write more efficient queries. With functions and operators specifically designed for arrays, SQL offers powerful tools for working with this versatile data type.

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

Privacy Policy