In PostgreSQL, we can use the array_append()
function to append an element to the end of an array.
The first argument is the array, and the second argument is the element to append.
Continue readingIn PostgreSQL, we can use the array_append()
function to append an element to the end of an array.
The first argument is the array, and the second argument is the element to append.
Continue readingIf you’re getting an error that reads “could not determine polymorphic type because input has type unknown” when using the array_shuffle()
function in PostgreSQL, it’s probably because your argument is of the wrong type.
The array_shuffle()
function requires an array as its argument. Passing a non-array value will result in an error. The actual error can vary, depending on the argument you pass, but either way, the error is usually due to a non-array argument being passed.
To fix this error, be sure to pass an array as the argument when calling the array_shuffle()
function.
PostgreSQL has an array_positions()
function that returns the position of the all occurrences of a given value in an array.
There’s also a array_position()
function (singular) that returns the position of just the first occurrence. So to get just the first occurrence, use that function instead.
If you’re getting an error that reads “multidimensional arrays must have array expressions with matching dimensions” it’s probably because you’re trying to create a multi-dimensional array where the inner arrays at the same level have different dimensions.
Multidimensional arrays must be rectangular. To fix this issue, make sure all arrays at the same level have the same dimensions.
Continue readingIn PostgreSQL, the array_length()
function returns the length of the specified array dimension.
We pass the array as the first argument, and the dimension as the second.
Continue readingIn PostgreSQL we can use the cardinality()
function to return the total number of elements in an array. This includes elements across all dimensions of the array.
We pass the array as an argument to the function, and it returns an integer of the total number of elements in that array.
Continue readingIf you’re getting a PostgreSQL error that reads something like “cannot subscript type text because it does not support subscripting” when selecting data from a database, it’s probably because you’re trying to perform some sort of array operation against a non array value.
The above error specifically mentions text data but we could get the same error when using a different data type, like an integer, character varying, etc.
To fix this issue, be sure to run the array operations against actual arrays. If working with non array data, then don’t use array operations against that data.
Continue readingIn PostgreSQL we can use the ARRAY
constructor to create an array. When we do this, we provide the array elements as a comma separated list, enclosed in square brackets. Postgres then works out the data type based on the array elements.
But what if we want to create an empty array?
Creating an empty array can cause issues if we don’t explicitly specify the type. We need to specify the type. Below is an example of creating an empty array in PostgreSQL using the ARRAY
constructor.
PostgreSQL has an array_position()
function that returns the position of the first occurrence of a given value in an array.
We have the option to specify a starting position, so that the function returns the position of the first occurrence after that.
To get all occurrences, use array_positions()
instead.
If you’re getting a PostgreSQL error that reads something like “function generate_subscripts(text[], integer, integer) does not exist“, it’s probably because your third argument is of the wrong type when using the generate_subscripts()
function.
The above error specifically implies that an integer was passed as the third argument, but it must be a boolean value.
The third argument of the generate_subscripts()
function is optional, but if passed, it must be a boolean expression.
To fix this error, either pass a boolean value as the third argument, or eliminate the third argument altogether.
Continue reading