Category: PostgreSQL

5 String Functions that Return Length in PostgreSQL

Posted on by Ian

PostgreSQL provides us with a handful of string functions that return the length of a given string.

But the result between these functions can be different, depending on which function we use. That’s because the “length” can be different, depending on what we’re measuring. Are we talking about the number of characters in the string? Or the number of bytes in the string? Or perhaps we want to know the number of bits in the string.

The function we use will depend on which of the above we’re trying to measure. Below are five functions that cater for each of the above questions.

Continue reading →

Create a Multi-Dimensional Array from a Query in PostgreSQL

Posted on by Ian

In PostgreSQL, we can use array constructors to create arrays that contain elements that we specify. We can populate the array from literal values, or we can let a query provide the values.

We can also create multi-dimensional arrays. When populating the array from a query, we can use multiple subqueries to create each sub-array. Therefore we can create a multi-dimensional array from a query.

Continue reading →

Using REGEXP_SPLIT_TO_ARRAY() in PostgreSQL

Posted on by Ian

In PostgreSQL, the regexp_split_to_array() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a text array.

So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.

We pass the string as the first argument and the pattern as the second. We can also specify a flag to determine how the function behaves.

Continue reading →

How REGEXP_SPLIT_TO_TABLE() Works in PostgreSQL

Posted on by Ian

In PostgreSQL, the regexp_split_to_table() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a table.

So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.

We pass the string as the first argument and the pattern as the second. We can also specify a flag to change the behaviour of the function.

Continue reading →

Fix Error “cannot take logarithm of a negative number” in PostgreSQL

Posted on by Ian

If you’re getting an error that reads “ERROR: cannot take logarithm of a negative number” when using either the log() function or log10() function in PostgreSQL, it’s probably because you’re passing a negative value to the function.

These functions require a value greater than zero. This is true even for the base argument of the log() function (the argument that specifies which base to use).

To fix this issue, be sure to pass a value greater than zero to these functions.

Continue reading →

Understanding PostgreSQL’s LOG10() Function

Posted on by Ian

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.

Continue reading →

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 →