We have several options when it comes to appending elements to arrays in PostgreSQL. We can use an operator to concatenate the value to the array or we can use a function to do the job.
Below are four ways to append elements to arrays in PostgreSQL.
arrays
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.
Using ARRAY_TO_STRING() in PostgreSQL
In PostgreSQL we can use the array_to_string() function to convert an array to a string. It converts each array element to its text representation (if needed), and then concatenates all elements using the given delimiter.
Any null values are omitted from the result, but we do have the option of replacing any null values with a given text value.
An Overview of UNNEST() in PostgreSQL
In PostgreSQL, we can use the unnest() function to expand an array into a set of rows. We pass the array as an argument, and the function returns each element on a separate row.
We can use the function on multi dimensional arrays, and it’s also possible to use it to unnest multiple arrays by including it in the FROM clause of a query.
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.
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.
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.
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.
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.
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.