How to Create Array in PostgreSQL

PostgreSQL allows you to store data in arrays. In this article, we will look at how to create array in PostgreSQL. You can use it to create and store arrays in PostgreSQL.


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.

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.

Also read : How to Convert String Case in PostgreSQL


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.

Also read : How to Concatenate Strings in PostgreSQL


Create Array with Range 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[]
);

Also read : How to Insert data into Array 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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform that makes it easy to get insights from data. Build dashboards, charts & reports for your business in minutes. Try it for free today!