PostgreSQL Arrays Tutorial

Variable-length one-dimensional or multi-dimensional arrays can be defined as a field of a table in PostgreSQL database. Built-in, user-defined, enum and composite data types are supported by postgreSQL array. Array fields are declared by using square brackets like other array declarations. For example, integer arrays can be declared as integer[], text arrays can be declared as text[] etc. How you can use postgreSQL arrays with create, select, insert, update and delete query are shown in this tutorials.

Example-1: Creating table with array field

Create a table using array field. In this example, a table named users is created with fields, Username, Fullname, Gender, Email, Phone and Interests. Interests field is defined as TEXT [] array which can accept multiple values in this field.

CREATE TABLE users (
Username VARCHAR(15) PRIMARY KEY,
Fullname VARCHAR (100),
Gender VARCHAR (6),
Email VARCHAR (20),
Phone VARCHAR (20),
Interests TEXT[]);

Example-2: Insert data in array field of the table

You can insert one or more values in the array field. In array fields, the multiple values are separated by commas and use second bracket to set the values. Run the following three insert statements to insert three rows in users table.

INSERT INTO users (Username, Fullname, Gender, Email, Phone, Interests)
VALUES (‘john99’, ‘Jonathan Bing’, ‘Male’, ‘[email protected]’, ‘+2455676677’,
‘{"Gardening", "Cricket", "Cycling", "Travelling"}’);

INSERT INTO users (Username, Fullname, Gender, Email, Phone, Interests)
VALUES (‘maria_hd’, ‘Maria Hossain’, ‘Female’, ‘[email protected]’, ‘+9234455454’,
‘{"Travelling", "Tennis"}’);

INSERT INTO users (Username, Fullname, Gender, Email, Phone, Interests)
VALUES (‘fahmidabd’, ‘Fahmida’, ‘Female’, ‘[email protected]’, ‘+88017348456’,
‘{"Programming", "Music"}’);

Run the following select statement to show all records of users table.

SELECT * FROM users;

Example-3: Select data using array field of the table

a) Retrieve particular array value

If you want to retrieve particular value from the array then you have to specify the index value with the array field. The following select query is used to retrieve Fullname, Phone and Interest value of the second index from the users table of all male users.

SELECT Fullname, Phone, Interests[2]
FROM users
WHERE Gender=‘Male’;

If the index value is unavailable in the array field then it will returns NULL as output. In the following statement, fifth index not exists in any Interests field.

SELECT Fullname, Email, Interests[5]
FROM users
WHERE Gender=‘Female’;

b) Retrieve records based on specific array value

You can use any function to search particular array values in all records of array field. The following select statement will retrieve Fullname, Email and Phone of all users whose Interests field contains ‘Travelling” value.

SELECT Fullname, Email, Phone
FROM users
WHERE ‘Travelling’ = any(Interests);

c) Retrieve records using unnest function

unnest() function is used to retrieve each array value in separate row. The following select statement will retrieve records separately for each value of Interests field.

SELECT Fullname, unnest(Interests)
FROM users;

Example-4: Update data using array field of the table

a) Update particular array index

The following update query will update the second index value of Interests in which records contains the username “john99”.

UPDATE users
SET Interests [1] = ‘Football’
WHERE Username= ‘john99’;

Run the following select query to show effect of the update query.

SELECT Username, Interests FROM users;

b) Update all values of a particular array field

You can update the full content of the array field by two ways. First way is to use normal conventional way which is shown in the above example and second way is to use array expression. Both ways are shown below for updating the record. You can run any of the following queries to update the array field. The following update statement will replace the previous values with new two values which row contains the username, ‘maria_db’.

UPDATE users
SET Interests = ‘{"Gardening", "Hockey"}’
WHERE Username= ‘maria_hd’;

Or,

UPDATE users
SET Interests = array [‘Gardening’,‘Hockey’]
WHERE Username= ‘maria_hd’;

Run the following select query to show effect of the update query.

SELECT Username, Interests FROM users;

Example-5: Delete data based on array field of the table

You can run delete query by matching particular array values. In the following example, the users who has “programming” as the value in the first index of Interests field will be deleted from the table.

DELETE FROM users
WHERE Interests[1] = ‘Programming’;

Run the following select query to show effect of the delete query.

SELECT Username, Interests FROM users;

All the above examples of this tutorial show the use of one-dimensional array in PostgreSQL. The use of multi-dimensional array is bit complicated than one-dimensional array. As a beginner, you can practice this tutorial to gain the basic knowledge of using PostgreSQL array.