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.
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.
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.
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.
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.
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.
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.
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”.
SET Interests [1] = 'Football'
WHERE Username= 'john99';
Run the following select query to show effect of the update query.
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’.
SET Interests = '{"Gardening", "Hockey"}'
WHERE Username= 'maria_hd';
Or,
SET Interests = array ['Gardening','Hockey']
WHERE Username= 'maria_hd';
Run the following select query to show effect of the update query.
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.
WHERE Interests[1] = 'Programming';
Run the following select query to show effect of the delete query.
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.
