Author: Ian

Fix ‘time zone … not recognized’ in PostgreSQL

Posted on by Ian

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 reading →

How MIN_SCALE() Works in PostgreSQL

Posted on by Ian

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.

Continue reading →

Understanding PostgreSQL’s REGEXP_MATCHES() Function

Posted on by Ian

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 reading →

How REGEXP_MATCH() Works in PostgreSQL

Posted on by Ian

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 →

Understanding PostgreSQL’s pg_input_error_info() Function

Posted on by Ian

PostgreSQL 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.

Continue reading →

Fix “function array_shuffle(numeric) does not exist” in PostgreSQL

Posted on by Ian

If 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.

Continue reading →