PostgreSQL Array Data Type

By /
Array Type In Postgresql

One of the best features of PostgreSQL is the array datatype. You can create and store an array of any datatype in PostgreSQL without doing any workaround. In this tutorial, we will learn everything about the PostgreSQL array with examples. So, let’s get started!

Introduction to PostgreSQL Array

In PostgreSQL, you can create a multi-dimensional variable-length array of any predefined or user-defined data type, enum type, composite type, range type or domain.

Unlike MySQL, where you have to use either json type or command-separated values in the string type column, you don’t need to do anything in PostgreSQL. Just simply specify the data type with the square brackets (to denote it’s an array) and that’s it.

Also Read: PostgreSQL Interval Data Type (With Examples)

Declaring an Array in PostgreSQL

Below is the syntax to declare an array for any datatype.

Syntax:

variableName DATA TYPE [];Code language: CSS (css)

where

  • variableName is the name of the array,
  • DATA TYPE is type of data it can stores

Example:

DROP TABLE IF EXISTS students;
CREATE TABLE students(
name VARCHAR(100),
phoneNumbers VARCHAR(10)[3],
favNumbers INTEGER[],
secretCode INTEGER[3][3]
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the ‘phoneNumbers’ column is of type varchar array having the fixed size three. The ‘favNumbers’ column is of a type integer array with no fixed size. Lastly, the ‘secretCode’ column is also of type integer but has a two-dimensional array with a size three by three.

Declaring an Array using the ARRAY keyword

Alternatively, you can specify a column of type array by using the keyword ‘ARRAY’ as shown below:

phoneNumbers VARCHAR(10) ARRAY[3]
favNumbers INTEGER ARRAYCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note: The current implementation of the array in PostgreSQL simply ignores the size, i.e., even if you specify a size limit, it can accept values of unspecified length.

Input Format for insert Array values

You can insert array values by wrapping them inside the curly brackets as shown below.

For one-dimensional array:

'{ val1 delim val2 delim ... }'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

where delim is the delimiter character, and for all built-in types, it is a comma(,).

For two-dimensional array:

'{{1,2,3},{4,5,6},{7,8,9}}'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Example:

Now let’s insert some values into the table.

INSERT INTO students (name, phoneNumbers, favNumbers, secretCode)
VALUES
('Alice', '{"5551234567", "5552345678", "5553456789"}', '{42, 13, 7}', '{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'),
('Bob', '{"5559876543", "5558765432", "5557654321"}', '{99, 88, 77}', '{{9, 8, 7}, {6, 5, 4}, {3, 2, 1}}'),
('Charlie', '{"5551112222", "5552223333", "5553334444"}', '{100, 200, 300}', '{{0, 1, 0}, {1, 0, 1}, {0, 1, 0}}');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the ‘phoneNumbers’ column contains exactly three values and the secretCode column contains exactly three by three values. Let’s check the table output now.

SELECT * FROM students;

Output:

Students Table Data
Students Table Data

As you can see, values are perfectly inserted!

Example 2:

Let’s now try to insert values more than the limit in the ‘phoneNumbers’ column and see if PostgreSQL allows it or not.

INSERT INTO students (name, phoneNumbers, favNumbers, secretCode)
VALUES 
('David', '{"5554445555", "5555556666", "5556667777", "7672671623"}', '{10, 20, 30}', '{{1, 0, 0}, {0, 1, 0}, {0, 0, 1}}');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The above query executes successfully. Let’s check the table output.

SELECT * FROM students;

Output:

Data More Than Limit
Data More Than Limit

As you can see, even if the ‘phoneNumbers’ column has an array limit of 3, it can hold more than that limit. That’s what we just learned earlier.

Insert values using the ARRAY Keyword

Postgresql offers another way as well to insert records in the array column by using the ARRAY keyword. Let’s see it.

Example:

INSERT INTO students (name, phoneNumbers, favNumbers, secretCode)
VALUES 
(
'Emily', ARRAY['5557778888', '5558889999', '5559990000'], 
ARRAY[50, 60, 70], ARRAY[[9, 9, 9], [8, 8, 8], [7, 7, 7]]
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s see if the data gets correctly inserted.

Output:

Insert Data Using Array Keyword
Insert Data Using Array Keyword

As you can see, we got the correct output.

Using WHERE clause with Array in PostgreSQL

Now you might be thinking, how can we use the WHERE clause with the array data? Let’s see.

If you want to search for something, make sure you search that key against an index of an array where the index in PostgreSQL starts from 1.

Let’s take an example of it.

Example:

We want to apply a search condition on the first row first column of the ‘secretCode’ column where the value is between 0 to 5. Let’s write a query for this.

SELECT * FROM students
WHERE secretCode[1][1] 
BETWEEN 0 and 5;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Access Array Data
Access Array Data

As you can see in the highlighted part of the output above, the query only returned the records as per the condition.

Access Array element using Index in PostgreSQL

You can also choose a particular index of an array to be displayed on the output window.

Example:

SELECT name, phoneNumbers[1], favNumbers, secretCode 
FROM students;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Access Particular Index
Access Particular Index

As you can see, the query only returned the first phone number from the array of numbers.

Modifying the Array Elements

You can also update the array values using the UPDATE statement.

Example:

UPDATE students
SET secretCode = '{{0, 0, 0}, {1, 1, 1}, {0, 0, 0}}'
WHERE name='Emily';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Modify Whole Array Data
Modify Whole Array Data

Here you can see, we have updated the whole array of data.

If you want to update only a particular element in the array, you can do it as well.

UPDATE students
SET favNumbers[1]=20
WHERE name='Alice';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Modify Particular Element In Array
Modify Particular Element In Array

As you can see, we have only updated a single value in the array using the above syntax.

Conclusion

In this tutorial, we have learned about the array data type in PostgreSQL. An array type is an interesting topic to learn and easy to master. Make sure you practice arrays as much as possible so that you can clear your doubts on your own. You can also read the official documentation for more in-depth information.

Reference

PostgreSQL Official documentation on Arrays