How to Create an Array in PostgreSQL

An array is a single data object that holds multiple values.

In PostgreSQL, you can create an array for any built-in or user-defined data type. However, an array can only contain one data type. This means you can have an array of strings, an array of integers, and the like, but you cannot have an array that has both integer and string types.

To create a column of an array type, the [] symbol is used. The following examples illustrate this:

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

create table player_scores (
	player_number integer,
	round_scores integer[]
);

PostgreSQL also allows multi-dimensional arrays by using multiple pairs of square brackets in the column definition. One requirement here, as you will see later, is that the inner dimensions must have the same array lengths. Here we create a two-dimensional array for the student scores:

create table student_scores (
	student_number integer,
	test_scores decimal[][]
);

To conform to the SQL standard, PostgreSQL also accepts the ARRAY keyword for declaring one-dimensional arrays. Shown below is an alternate way to create the contacts and player_scores tables:

create table contacts (
	first_name varchar,
	last_name varchar,
	phone_numbers varchar array
);

create table player_scores (
	player_number integer,
	round_scores integer array[10]
);

Note the array[10] in player_scores above. PostgreSQL allows you to specify an array size limit, whether you use the datatype[] or datatype array[] pattern. However, this is just to conform to the SQL standard. As of PostgreSQL 12.2, this is quietly ignored and is not enforced.

Arrays are typically used when a field can have multiple values for an entity, and the values are on their own "complete" and do not merit that they be put to another table for a one-to-many relationship.