How to Create Array in PostgreSQL

Last updated on July 8th, 2024 at 09:23 am

PostgreSQL is a popular database that supports a vast number of data types. It allows web developers and database programmers to easily build any kind of website or app around their data. It even allows you to store data in arrays. Arrays are complex data types used to store a large amount of information compactly. In this article, we will look at how to create array in PostgreSQL. You can use it to create and store arrays in PostgreSQL. There are several different ways to define arrays and insert data into them. Unless you are aware of them and know their syntax, you will often encounter errors in your SQL queries while working arrays.

What is Array in PostgreSQL

PostgreSQL array is a special type of column where each value consists of a sequential list of items. Each of these items can be a built in data type like integer or varchar, or a user-defined data type. This is very useful for modern software development such as websites and apps that extensively use arrays. PostgreSQL not only provides several ways to define arrays and insert data but also many ways to update arrays.

How to Create Array in PostgreSQL

PostgreSQL arrays can consist of built-in or user defined data types. But all elements of an array must be of same data type. For example, you can create array of integers or array of dates, but you cannot create an array with both integers and dates in it.

1. Using Square Brackets

You can easily create arrays in PostgreSQL by adding square brackets [] immediately after the data type for the column.

Here is an example to create integer array column

create table employees ( 
     first_name varchar, 
     last_name varchar, 
     phone_numbers integer[] 
);

In the above example, we have created column phone_numbers as an array of integers. As you can see, we need to specify the data type of array items, immediately after the column name, followed by square brackets. So it is implied that all items of array column have same data type.

2. Using Array keyword

Alternatively, you can also use array keyword, instead of square brackets to define array.

create table employees ( 
     first_name varchar, 
     last_name varchar, 
     phone_numbers integer array
);

3. Create Array with Size in PostgreSQL

You can also create an array of definite size by specifying array size limit. Here is an example that uses integer array of size 10.

create table employees ( 
     first_name varchar, 
     last_name varchar, 
     phone_numbers integer[10] 
);

OR

create table employees ( 
     first_name varchar, 
     last_name varchar, 
     phone_numbers integer array[10] 
);

Whether you use datatype[] or datatype array[] format to define an array column, you can choose to specify size limit. But please note, from PostgreSQL 12.2 it is ignored and not enforced.

4. Create Multi-dimensional Array in PostgreSQL

Similarly, it is very easy to create multi-dimensional array in PostgreSQL. You just need to add multiple pairs of square brackets after the data type. For example, you need to add two pair of square brackets [][] to create a two-dimensional array in PostgreSQL.

create table employees ( 
     first_name varchar, 
     last_name varchar, 
     contacts integer[][] 
);

In the above example, contacts column is a two-dimensional array of integers. Please note, in this case, each sub-array in main array needs to be of same length. You can use the same logic to create array with more dimensions, such as 3, 4, 5, etc. dimensions, depending on your requirement.

Once you have created an array in PostgreSQL, you can also learn how to insert into array and how to update array.

Conclusion

Arrays are a versatile data structure used to save multiple values of an entity in a database. It is a big relief that a popular database like PostgreSQL supports arrays. But it is important to know the different ways to create arrays, correct syntax to define an array, along with the limitations in each approach. Otherwise, you will frequently get errors in your SQL queries.

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 Convert String Case in PostgreSQL
How to Insert data into Array in PostgreSQL
How to Concatenate Strings in PostgreSQL