In PostgreSQL we can use the array_fill() function to create an array filled multiple instances of a given value.

We pass the value to populate as the first argument, followed by the length of the desired array. The resulting array replicates the first argument as specified by the length in the second argument.

We also have the option of passing a third argument to specify the lower bound values of each dimension of the array.

Example

Here’s a quick example to demonstrate:

SELECT array_fill( 3, ARRAY[4]);

Result:

{3,3,3,3}

We can see that the resulting array consists of the first argument (3) repeated four times, as specified in the second argument.

Generate a Multi Dimensional Array

The following example results in a multi dimensional array being generated:

SELECT array_fill( 3, ARRAY[4,2]);

Result:

{{3,3},{3,3},{3,3},{3,3}}

Here’s another one:

SELECT array_fill( 2, ARRAY[3,4]);

Result:

{{2,2,2,2},{2,2,2,2},{2,2,2,2}}

Specify a Lower Bound

We can add a third argument to specify the lower bound of the resulting array:

SELECT array_fill( 3, ARRAY[4], ARRAY[11] );

Result:

[11:14]={3,3,3,3}

The [11:14]= part is subscripted assignment that specifies the subscript range of the array. By default, PostgreSQL arrays are one-based (which means their subscripts start at 1). Subscripted assignment like the above enables us to change the lower bound so that the array is no longer one-based. We can use this method to generate zero-based arrays if required.

Here’s an example with the multi dimensional array:

SELECT array_fill( 2, ARRAY[3,4], ARRAY[0,5] );

Result:

[0:2][5:8]={{2,2,2,2},{2,2,2,2},{2,2,2,2}}

Explicitly Casting the Type

Sometimes we might need to explicitly specify the data type of the value that we provide for the array. For example:

SELECT array_fill( 'Go'::text, ARRAY[4] );

Result:

{Go,Go,Go,Go}

Here’s what happens in this case if I don’t specify the data type:

SELECT array_fill( 'Go', ARRAY[4] );

Result:

ERROR:  could not determine polymorphic type because input has type unknown