How ARRAY_FILL() Works in PostgreSQL

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.

Continue reading

Understanding STRING_TO_ARRAY() in PostgreSQL

In PostgreSQL, string_to_array() is a string function that allows us to create an array from a string. It splits the string based on the specified delimiter and returns a text array as a result.

If we specify a null delimiter, then each character becomes an element in the array. If the delimiter string is empty, then the whole string becomes a single element in the array.

We also have the option of turning a specific substring into null if required.

Continue reading

Fix “function array_sample(numeric, integer) does not exist” in PostgreSQL

If you’re getting an error that reads “function array_sample(numeric, integer) does not exist” when using the array_sample() function in PostgreSQL, it’s probably because your first argument is a numeric type instead of an array.

The first argument for this function must be an array.

To fix this error, be sure that your first argument to array_sample() is an array, not a numeric value.

Continue reading

Fix “function array_shuffle(integer) does not exist” in PostgreSQL

If you’re getting an error that reads “function array_shuffle(integer) does not exist” when using the array_shuffle() function in PostgreSQL, it’s probably because the argument you’re passing to the function is not an array.

More specifically, this error message implies that you’re passing an integer. The same error can occur when passing other non-array types (such as numeric), but the solution is the same.

The argument for this function must be an array.

To fix this error, replace the integer value with an array value when calling the array_shuffle() function.

Continue reading

Fix “source array too small” When Updating an Array in PostgreSQL

If you’re getting an error that reads “source array too small” when updating an array in PostgreSQL, it’s probably because you’re passing a slice that’s smaller than your specified range. For example, if you specify a range that covers four elements but provide an array that contains just three elements, then you’d get this error.

To fix the error, be sure to provide an an array that contains at least as many elements as are specified in the range.

Continue reading

Fix “function array_shuffle(integer, integer) does not exist” in PostgreSQL

If you’re getting an error that reads “function array_shuffle(integer, integer) does not exist” when using the array_shuffle() function in PostgreSQL, it’s probably because the argument you’re passing to the function is not an array.

Actually, the error message implies that you’re passing multiple integer values (two in this case, but it could be more), separated by a comma. Perhaps you’re trying to pass an array of integers, but you haven’t provided them in an array. Or perhaps you’re passing the wrong column or variable. Either way, the argument for this function must be an array.

To fix this error, replace the integer values with a single array value when calling the array_shuffle() function.

Continue reading

Difference Between ARRAY_POSITIONS() and ARRAY_POSITION() in PostgreSQL

PostgreSQL has a couple of functions that enable us to get the position or positions of a given value in an array. One of them – array_position() – returns the position of just the first occurrence (as an integer), while the other function – array_positions() – returns the position of all occurrences (as an array).

So these functions are aptly named – one is singular and the other is plural.

But this isn’t the only difference. The array_position() function allows us to specify a start position, while array_positions() doesn’t.

Continue reading