In 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 readingAuthor: Ian
A Quick Look at TRIM_ARRAY() in PostgreSQL
In 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 readingFix Error “cannot determine type of empty array” in PostgreSQL
If 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 reading4 Ways to Append an Element to an Array in PostgreSQL
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.
Continue readingHow 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.
Continue readingUsing 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.
Continue readingAn 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.
Difference Between QUOTE_LITERAL() and QUOTE_NULLABLE() in PostgreSQL
On the surface, PostgreSQL’s quote_literal() and quote_nullable() functions appear to do the same thing. But there is a difference.
It all comes down to how they deal with null arguments; quote_literal() returns null while quote_nullable() returns the string NULL.
Fix Error “time field value out of range” when using make_time() in PostgreSQL
If you’re getting an error that reads ‘time field value out of range‘ in PostgreSQL while using the make_time() function, it’s probably because one or more of the time parts you’re providing is out of the accepted range for that time part.
To fix this issue, be sure that each time part you provide is within the valid range for that time part.
Continue readingUnderstanding the NORMALIZE() Function in PostgreSQL
PostgreSQL has a normalize() function that converts a string to the specified Unicode normalization form.
The function can only be used when the server encoding is UTF8.