Array Constructors

In PostgreSQL, an array constructor is a method for creating arrays, which are collections of data with similar datatype, they can be one-dimensional, two-dimensional, multi-dimensional, or nested. These arrays can then be stored and manipulated in the postgres database.

Syntax

Array constructor syntax are defined by appending [] to the data type name of the array elements. The data type name signifies the datatype of the array.

column_name datatype []

Example 1

To create an array with a specific element, you can use the ARRAY keyword, or you can write arrays as string literals by using curly braces {}.

-- This creates a table named 'example1' with two columns: 'id' and 'numbers'.
-- 'id' is an auto-incrementing primary key, and 'numbers' can hold a collection of integers.
CREATE TABLE example1 (id SERIAL PRIMARY KEY, numbers INTEGER[]);
-- using the 'ARRAY' keyword to insert integer arrays into the number column
INSERT INTO example1 (numbers) VALUES (ARRAY[1, 2, 3, 4]);
-- using string literals to insert integer arrays into the number column
INSERT INTO example1 (numbers) VALUES ('{5, 6, 7, 8}');

When the example above is run it would create a a table name called example1 with 2 rows. The numbers column contains a one-dimensional array of integers.

example1 table:

Example 2

Now let’s create a new table called example 2 with team column and add a two-dimensional values of type text:

-- This creates a table named 'example1' with two columns: 'id' and 'numbers'.
-- 'id' is an auto-incrementing primary key, and 'team' can hold a collection of text.
CREATE TABLE example2 (id SERIAL PRIMARY KEY, team text[][]);
-- Using the 'ARRAY' keyword to insert text arrays into the 'team' column
INSERT INTO example2 (team) VALUES (ARRAY[['Tolu','Sophie'],['Chloe','Kemi']]);
-- using string literals to insert integer arrays into the number column
INSERT INTO example2 (team) VALUES ('{{"Kim","Tayo"},{"Achur","Timi"}}');

122 example2 table:

Example 3

We have now successfully learned how to insert arrays into our postgres table, now let’s learn how to access our array element using the example3 table below:

-- This creates a table named 'example3' with three columns: 'id', 'names' and contacts.
-- 'id' is an auto-incrementing primary key, 'names' hold text values and 'contacts' can hold a collection of text.
CREATE TABLE example3 (id SERIAL PRIMARY KEY, names text, contacts text[]);
-- Using the 'ARRAY' keyword to insert text arrays into the 'contacts' column
INSERT INTO example3 (names, contacts) VALUES ('Tolu', ARRAY['(+234)81-1765-3345','(+234)81-1265-3347']);
-- using string literals to insert text array into the 'contacts' column
INSERT INTO example3 (names, contacts) VALUES ('Micheal','{"(+234)71-1700-3935","(+234)81-1905-0007"}');

example3 table:

We can access the array elements using array subscript numbers which are written between square brackets.

Accessing the example3 table

By using the array subscript number we can select all first item on the contacts column using the script below:

SELECT  names, contacts[ 1 ] FROM example3;

Our queried table:

We can also use array subscript number with the where statements to filter out the result rows

SELECT names FROM example3 WHERE contacts[ 2 ] = '(+234)81-1905-0007';

Our queried table:

Finally, let’s try updating our table using the same idea, with the following script

UPDATE example3 SET Contacts[1]='(+234)81-0000-0000' WHERE names = 'Tolu';

Now, if we check our example3 table again we will find out that the contact (+234)81-1265-3347 has been changed to (+234)81-0000-0000 in the table below:

SELECT * FROM example3

Our queried table: