How to Insert into Array in PostgreSQL

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!

mm

About Ubiq

Ubiq is a powerful business intelligence & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!