In PostgreSQL, we have many ways to retrieve data from arrays. One way is to specifically reference its subscript or a range of subscripts. Another way is to search through the array’s contents for the value we want.
By doing a search, I mean we could do things like filter the query with a WHERE clause, so that we only return rows that contain an array that has an element with a certain value.
In PostgreSQL, the array_prepend() function prepends an element to the start of an array.
The first argument is the element to prepend, and the second argument is the array.
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, we can use the string_to_table() function to return a set of rows, each containing a part of the string. The string is split based on the specified delimiter.
If we specify a null delimiter, then each character becomes a separate row in the output. If the delimiter string is empty, then the whole string is returned in a single row.
We also have the option of turning a specific substring into null if required.
In 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 readingPostgreSQL has a datestyle variable that specifies the display format for date and time values, as well as the rules for interpreting ambiguous date input values.Â
We can set the date/time style with the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.
Below is an example of using the SET datestyle command to change the datestyle for the current session.
In 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 reading