How to Insert into Array in PostgreSQL

Last updated on July 4th, 2024 at 09:09 am

PostgreSQL supports a wide range of data types including arrays. In this data type, the column value for each row is an array of items, of same or different data types. If you have never worked with array data type before in PostgreSQL, then you may be wondering how to define such a column in a table, and also how to insert data into such a column. Sometimes you may even 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

It is very important to understand how to correctly insert data into array column in PostgreSQL. Otherwise, it will give you an error. In this case, the error messages are subtle and it is difficult to understand what went wrong. Let us say you have the following table.

CREATE TABLE sal_emp (
name            text,
quarter  integer[]
); 

The following INSERT query will give error.

postgres-# INSERT INTO sal_emp(name, quarter[])
VALUES ('Bill',
'{1, 2, 3}');

ERROR:  syntax error at or near "'"

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[] 
);

As highlighted above, you need to add [] at the end of data type of each item in the array. That will create array in PostgreSQL. In our example, phone_numbers column is an array where each item has varchar data type. Similarly, if you want to define an array of integer items, you can define it as int[]. There are two accepted syntaxes to insert into array in PostgreSQL – one using ARRAY keyword, and other using quotes & curly braces.

1. Using ARRAY keyword

In this approach, we specify the array value using ARRAY keyword followed by array item values in square brackets […]. Here is the SQL query to insert into array in PostgreSQL using ARRAY keyword. Please note, during data insertion you just need to mention column name phone_numbers without any [] character afterwards.

# 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.

2. 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 enter data into array 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.

3. Insert array of objects

PostgreSQL also allows you to define multi dimensional arrays. For this, you can 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, using single quotes and curly braces. Since sales is a 2 dimensional array, you need to provide nested curly braces. Each item within outer curly braces is itself another array and therefore enclosed in curly braces.

# 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.

Conclusion

In this article, we have learnt a couple of different ways to enter data into array in PostgreSQL database. We are basically using the same old insert statement to insert values. The key thing is how to present your array value in the insert statement. Instead of specifying values within quotes or any other character, we either use ARRAY keyword, followed by comma-separated array items enclosed within square brackets, or mention comma-separated array items in curly braces. If your column is a multi-dimensional array, then you need to use nested curly braces since the each item of array will also be an array. This approach is different from updating array items in PostgreSQL.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Create Arrays in PostgreSQL
How to Concatenate Strings in PostgreSQL
How to Convert String Case in PostgreSQL