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!