In 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 readingHow PostgreSQL’s SOME() Construct Works
PostgreSQL has the SOME() construct that we can use to perform searches against arrays. It returns a Boolean result, and so we can incorporate this into our searches to return just those rows that yield true or false.
SOME() is a synonym for ANY(), and so we can use either one to get the same result.
Understanding PostgreSQL’s LOG10() Function
In PostgreSQL, log10() is a mathematical function that returns the base 10 logarithm of its argument.
It was added in PostgreSQL 12 as a SQL-standard equivalent of the existing log() function.
There is a difference between the two functions though, because log() allows us to specify which base to use (although it defaults to base 10), whereas log10() uses base 10 only.
Fix ‘time zone … not recognized’ in PostgreSQL
If you’re getting an error that reads something like ‘time zone “US/New_York” not recognized‘ in PostgreSQL while using a function such as date_add(), date_subtract() or date_trunc(), it’s probably because you’re specifying an unsupported time zone.
If you specify a time zone, it must be one that’s recognised by the system.
To fix this issue, either provide a recognised time zone or omit the time zone argument altogether (assuming this is a valid option for the situation).
Continue readingUsing TRIM_SCALE() in PostgreSQL
In PostgreSQL, we can use the trim_scale() function to remove any trailing zeros from the end of the fractional part.
This doesn’t change the value, it merely reduces the number characters whenever there are unnecessary zeros on the end.
Continue readingHow MIN_SCALE() Works in PostgreSQL
In PostgreSQL, min_scale() is a mathematical function that returns the minimum scale needed to represent the supplied value precisely.
The “scale” is the number of fractional decimal digits. For example, a value of 1.9500 has a minimum scale of 2, whereas 1.957 has a minimum scale of 3.
We pass the value to the function as a numeric argument, and it returns the result as an integer.
2 Functions that Return a Substring in PostgreSQL
PostgreSQL has an extensive collection of built-in string functions that allow us to manipulate text values. One task we might want to perform is to extract a substring from a larger string. Fortunately, PostgreSQL caters for our needs with at least two functions that make this a breeze.
Continue readingA Quick Look at PostgreSQL’s REVERSE() Function
In PostgreSQL, we can use the reverse() function to return a specified string with its characters reversed. In other words, the string is returned with its characters in the reverse order to how we provided them.
Understanding PostgreSQL’s REGEXP_MATCHES() Function
In PostgreSQL, the regexp_matches() function returns substrings that match a given POSIX regular expression in a given string. We can specify that all matches are returned or just the first match. If all matches are returned, each is returned in a separate row.
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.
Each returned row is a text array containing the whole matched substring or the substrings matching parenthesised subexpressions of the pattern.
Continue readingHow REGEXP_MATCH() Works in PostgreSQL
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 reading