PostgreSQL allows users to create arrays of any data type like INTEGER, TEXT, DATE, etc. In Postgres, Arrays allow us to store data of any inbuilt, user-defined, or enumerated data type. However, the data Stored in an array must be of the same type. For instance, in Postgres, an array can be created with the INTEGER data type, TEXT data type, DATE data type, etc., but neither can be combined into one array.

This write-up will show you how to create arrays in PostgreSQL via practical examples. So, let's get started.

How to Create Arrays in PostgreSQL?

To create an array in Postgres, users must specify the column name, then the data type, followed by the square brackets “[]”. For instance, the below syntax will create an array at the time of table creation:

CREATE TABLE tbl_name(
col_1 data_type[]
);

Here, in the above snippet, tbl_name represents the table name, col_1 represents the column name, and data_type indicates the data type of the array. The square brackets after the data_type depict that it’s an array.

Example 1: Creating Arrays in Postgres

Let’s create a sample table named staff_data with four columns: st_id, st_name, st_phone, st_email:

CREATE TABLE staff_data(
st_id INT PRIMARY KEY,
st_name TEXT,
st_phone INT[],
st_email VARCHAR[]
);

The table named staff_data has been created successfully. Let’s insert the below-listed records into the “staff_data” table:

INSERT INTO staff_data(st_id, st_name, st_phone, st_email)
VALUES
(1, 'Mike', '{1234567811, 1112223312}', '{"mike123@gmail.com", "mike123@hotmail.com"}'),
(2, 'Tim', '{1899872311, 2123124551}', '{"tim123@gmail.com", "tim123@hotmail.com"}');

To verify the newly inserted data, execute the SELECT statement as follows:

SELECT * FROM staff_data;

This is how you can create an array and insert data into that array in PostgreSQL.

Example 2: Creating Arrays of Specific Range in Postgres

Specify the range within the square brackets to create arrays of a specific range:

CREATE TABLE staff_detail(
st_id INT PRIMARY KEY,
st_name TEXT,
st_exm BIGINT[13]
);

An array with a specified range has been created. It will accept less than or equal to 13 digits:

INSERT INTO staff_detail(st_id, st_name, st_exm)
VALUES
(1, 'Mike', '{1234567811, 4351112223312}'),
(2, 'Tim', '{121899872311, 2156231245517}');

Two records have been inserted into the “staff_detail” table. Let’s fetch the newly inserted data via the SELECT command:

SELECT * FROM staff_detail;

This way, the arrays with specified range work in Postgres.

How to Create 2-D Arrays in Postgres?

To create a multidimensional array in Postgres, specify the column name and then the data type, followed by two sets of square brackets:

CREATE TABLE tbl_name(
col_1 data_type[][]
);

Let’s comprehend the concept of 2-d arrays via a practical example:

Example: Creating 2-D Arrays in Postgres

Let’s learn how to create a multidimensional array in Postgres:

CREATE TABLE staff_info(
st_id INT PRIMARY KEY,
st_name TEXT,
st_exm INT[][]
);

A 2-D array has been created successfully. To insert the data into the “staff_bio” table, run the below command:

INSERT INTO staff_bio(st_id, st_name, st_exm)
VALUES
(1, 'Ambrose', '{{323, 111},{222, 555}}'),
(2, 'Joseph', '{{433, 421},{243, 385}}'),
(3, 'Dean', '{{543, 511},{712, 505}}');

This is how the 2-D arrays work in Postgres.

Conclusion

To create an array in Postgres, users must specify the column name, then the data type, followed by the square brackets “[]”. You can create an array with a range; to do so, Specify the range within the square brackets. To create a multidimensional array in Postgres, specify the column name and then the data type, followed by two sets of square brackets. This blog post presented an in-depth overview of the Postgres array through practical examples.