Database Management Systems
In PostgreSQL, the regexp_match() function returns a text array containing substrings that match a given POSIX regular expression in a given string. Only the first match is returned (to return all matches, use regexp_matches() instead).
We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.
Continue readingPostgreSQL has a pg_input_error_info() function that allows us to see the error details that would be returned if we were to pass an invalid value for the specified data type.
Basically it tests whether the given string is valid input for the specified data type. If invalid, it returns the details of the error that would have been thrown. If valid, the result is null.
In PostgreSQL, the pg_input_is_valid() function tests whether the given string is valid input for the specified data type.
It returns a boolean value of either true or false.
This function only works if the data type’s input function has been updated to report invalid input as a “soft” error.
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 regexp_substr() function to return a substring from a string based on a POSIX regular expression.
We can get the first occurrence or any other subsequent occurrence that matches the expression.
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 readingWe 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.
Continue readingIn 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