PostgreSQL array_fill() Function

The PostgreSQL array_fill() function returns an array filled with the specified elements.

`array_fill()` Syntax

Here is the syntax of the PostgreSQL array_fill() function:

array_fill(element, demension_length[, lower_bound]) -> array

element: Required. The element to fill into the array.
demension_length: Required. The dimensions of the array to be returned and the length of each dimension. It is a one-dimensional integer array. For example, ARRAY[2] represents a one-dimensional array with a length of 2; ARRAY[2,3] represents a two-dimensional array whose first dimension has a length of 2 and its second dimension has a length of 3.
lower_bound: Optional. The starting index of the array. It is a one-dimensional integer array and it should correspond to demension_length. For example, ARRAY[2] means that the one-dimensional array’s starting index is 2; ARRAY[2,3] means that the first dimension array’s starting index is 2, and the second dimension array’s starting index is 3. The default is 1.

The PostgreSQL array_fill() function returns an array whose dimensions are specified by demension_length and filled with element.

If the element is ambiguous, the array_fill() function returns an error: “could not determine polymorphic type because input has type unknown”.

This example shows how to use the PostgreSQL array_fill() function to get a one-dimensional array filled with 1.

SELECT array_fill(1, ARRAY[5]);

 array_fill
-------------
 {1,1,1,1,1}

You can also specify the starting index of the array. If you want to get a one-dimensional array and its index starts from 3.

SELECT array_fill(1, ARRAY[5], ARRAY[3]);

    array_fill
-------------------
 [3:7]={1,1,1,1,1}

This example shows how to use the PostgreSQL array_fill() function to get a two-dimensional array filled with 1.

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

     array_fill
---------------------
 {{1,1},{1,1},{1,1}}

Likewise, you can also specify a starting index for the two-dimensional array, for example:

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

           array_fill
--------------------------------
 [2:4][2:3]={{1,1},{1,1},{1,1}}

It is also possible to generate a three-dimensional array using the PostgreSQL array_fill() function, for example:

SELECT array_fill(1, ARRAY[2, 2, 2],  ARRAY[2, 2, 2]);

                  array_fill
-----------------------------------------------
 [2:3][2:3][2:3]={{{1,1},{1,1}},{{1,1},{1,1}}}

If you want to get an array filled with x. You may do that like:

SELECT array_fill('x', ARRAY[3, 2]);

You will get an error: “The polymorphic type could not be determined because the input type is unknown”.

You need to provide an exact type for x:

SELECT array_fill('x'::TEXT, ARRAY[3, 2]);

     array_fill
---------------------
 {{x,x},{x,x},{x,x}}