PostgreSQL allows us to create arrays and store them in the database as an array type. We can then go back later and update those arrays as required.
We can either replace the whole array, append or prepend elements, or update individual elements within the array. When we update individual elements, we can update one element or a whole slice. Below are examples of updating an array in PostgreSQL.
Continue readingBy default, PostgreSQL arrays are one-based. This means that we need to use 1 if we want to reference the first element in the array, 2 for the second, and so on.
But we also have the option of specifying our own subscript range for an an array. For example we could create a zero-based array, a ten-based array, or even a negative value such as a negative ten-based array.
We can do this by using subscripted assignment to specify the actual subscript range for the array. Basically, we prefix the array with the subscript range, enclosed in square brackets, and an equals sign (=) between it and the array.
If you’re updating arrays in PostgreSQL and you’ve suddenly realised that some of the arrays aren’t being updated, it could be due to the following.
If you’ve been using the array_append() function and the array_prepend() function, you may have found that one function works but the other doesn’t. For example array_append() works but array_prepend() doesn’t, or vice-versa.
In PostgreSQL, we can use array constructors to create arrays that contain elements that we specify. We can populate the array from literal values, or we can let a query provide the values.
We can also create multi-dimensional arrays. When populating the array from a query, we can use multiple subqueries to create each sub-array. Therefore we can create a multi-dimensional array from a query.
Continue readingIn PostgreSQL, the regexp_split_to_array() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a text array.
So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.
We pass the string as the first argument and the pattern as the second. We can also specify a flag to determine how the function behaves.
Continue readingIn PostgreSQL, an array constructor is an expression that builds an array value using values for its member elements.
One of the things we can do is build an array based on the results of a subquery.
Continue readingIf you’re getting an error that reads “function array_shuffle(numeric) 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 a numeric type. The same error can occur when passing other non-array types (such as integer), but the solution is the same.
The argument for this function must be an array.
To fix this error, replace the numeric value with an array value when calling the array_shuffle() function.
In PostgreSQL we can use the array_replace() function to replace a specified element (or elements) in an array with another element.
The first argument is the array, the second is the element to replace, and the third is the element to replace it with.
Continue readingIn PostgreSQL, we can use the trim_array() function to trim a given number of elements from the end of an array.
The first argument is the array, and the second is the number of elements to trim.
Continue readingIf you’re getting an error that reads “cannot determine type of empty array” in PostgreSQL, it could be that you’re trying to create an empty array without specifying the array type.
It’s impossible to create an array with no type, and so if we want to create an empty array, then we need to specify the type or add items to determine the type.
To fix this issue, either specify the array type for the empty array, or add items to the array.
Continue reading