Sometimes you may need to select into array, append to array, or insert array of objects in PostgreSQL. In this article, we will look at how to insert into array in PostgreSQL.
How to Insert into Array in PostgreSQL
Here are the steps to insert into array in PostgreSQL. Let us say you have the following table employees(id,name, phone_numbers) such that phone numbers column is an array of strings.
create table employees (id int,name varchar,phone_numbers varchar[]);
Also read : How to Create Arrays in PostgreSQL
There are two accepted syntaxes to insert into array in PostgreSQL – one using ARRAY keyword, and other using quotes & curly braces.
Using ARRAY keyword
Here is the SQL query to insert into array in PostgreSQL using ARRAY keyword.
# insert into employees (id, name, phone_numbers)
values (1, ' John Doe', ARRAY ['9998765432','9991234567']);
# select * from employees;
id | name | phone_numbers
----+-----------+-------------------------
1 | John Doe | {9998765432,9991234567}
In the above query, we use ARRAY [‘9998765432′,’9991234567’] to insert array into PostgreSQL column.
Also read : How to Convert String Case in PostgreSQL
Using single quotes & curly braces
We can also insert into PostgreSQL array using single quotes and curly braces as shown below.
# insert into employees (id, name, phone_numbers)
values (2, ' Jim Doe', '{"9996587432","9891334567"}');
# select * from employees;
id | name | phone_numbers
----+-----------+-------------------------
1 | John Doe | {9998765432,9991234567}
2 | Jim Doe | {9996587432,9891334567}
In the above example, we use ‘{“9996587432″,”9891334567”}’ to insert into array in PostgreSQL.
Also read : How to Concatenate Strings in PostgreSQL
Please note, in this case, the curly braces need to be enclosed inside single quotes. Also, since we are inserting string values, they need to be enclosed in double quotes, instead of single quotes, else you will get a syntax error.
Insert array of objects
You can also insert array of objects if you have an array of array column in PostgreSQL. Let us say you have the following table such that sales column is an array of array.
create table employees (id int,name varchar,sales int[][]);
Here is the SQL query to insert array of objects in PostgreSQL.
# insert into employees (id, name, sales)
values (2, ' Jim Doe', '{{150,100},{200,100}}');
# select * from employees;
id | name | sales
----+----------+-----------------------
2 | Jim Doe | {{150,100},{200,100}}
Please note, you need to enclose each array object in separate curly braces, inside the main curly braces and single quotes, ‘{{150,100},{200,100}}’ else you will get an error.
Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
