Tag: what is

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 →

How ARRAY_FILL() Works in PostgreSQL

Posted on by Ian

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