In PostgreSQL, we can create arrays of any type while table creation, such as INT, TEXT, VARCHAR, etc. Using arrays in Postgres, we can store data of any predefined, user-defined, and enumerated type. Once an array is created in Postgres, you can perform any specific operation on the array, such as inserting data into an array, appending to an array, fetching the array’s data, etc.

Several approaches for inserting data into a Postgres array will be explained in this write-up via practical examples.

Create a Sample Table

The below snippet shows how to create an array column during table creation:

CREATE TABLE employee_data(
emp_id INT PRIMARY KEY,
emp_name TEXT,
emp_email_address VARCHAR[]
);

The desired table has been created successfully. You can execute the SELECT statement to check the table’s structure:

SELECT * FROM employee_data;

The output shows that the “employee_data” table has three columns.

Inserting Data Into Postgres Arrays

Several syntaxes can be used to insert data into a Postgres array, such as using the ARRAY keyword with square brackets “[]” or curly braces enclosed within single quotes.

Syntax 1: ARRAY Keyword With Square Brackets “[]”

The below syntax explains how to insert data into an array using the ARRAY keyword:

INSERT INTO table_name (col_list)
VALUES (ARRAY ['val_1','val_2']);

The above syntax shows that the INSERT INTO statement is used to insert the data into an array in Postgres.

Example 1: Inserting Data Into an Array Using ARRAY Keyword

Let’s learn how to insert data into any particular array using the ARRAY keyword:

INSERT INTO employee_data (emp_id, emp_name, emp_email_address)
VALUES (1, 'Alex Root', ARRAY ['alex@xyz.com','ceo@xyz.com']),
(2, 'Tim Joseph', ARRAY ['tim@xyz.com', 'joseph@xyz.com']),
(3, 'Mike Ambrose', ARRAY ['ambrose@xyz.com']),
(4, 'Seth Jones', ARRAY ['seth@xyz.com']),
(5, 'Stephenie Tylor', ARRAY ['stephenie@xyz.com','hr@xyz.com']);

Five records have been inserted into the employee_data table. Let’s verify the data insertion via the SELECT query:

SELECT * FROM employee_data;

This is how you can insert the data into an array in Postgres.

Syntax 1: Using Curly Braces

The below syntax explains how to insert data into an array using the curly braces:

INSERT INTO table_name (col_list)
VALUES ('{val_1, val_2, …, val_n}');

Let’s understand it practically!

Example: Inserting Data Into an Array Using Curly Braces

In this example, we will explain how to insert data using curly braces syntax:

INSERT INTO employee_data (emp_id, emp_name, emp_email_address)
VALUES (6, 'Ben Stokes', '{"ben@xyz.com","stokes@xyz.com"}'),
(7, 'Mitchel Marsh', '{"mitch@xyz.com", "marsh@xyz.com"}'),
(8, 'Paul Allen', '{"paul@xyz.com"}');

Let’s verify the data insertion using the SELECT statement:

SELECT * FROM employee_data;

The output proves that the data has been inserted into the array successfully.

Conclusion

In PostgreSQL, several syntaxes can be used to insert data into an array, such as using the ARRAY keyword with square brackets “[]” or curly braces enclosed within single quotes. Any of these syntaxes can be used with the INSERT INTO statement to insert data into a Postgres array. This write-up explains how to insert data into a Postgres array using suitable examples.