7

Problem

This code:

select
  x::text
from
  regexp_matches( 'i1 into o2, and g1 into o17', '[gio][0-9]{1,}', 'g' ) as x;

Returns these results:

{i1}
{o2}
{g1}
{o17}

Rather than the following results:

i1
o2
g1
o17

Related Links

Question

What is the most efficient way to remove the braces using PostgreSQL 9.x?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315

1 Answers1

8

Optimal Solution

Your regexp_matches() pattern can only result in a single element per pattern evaluation, so all resulting rows are constrained to exactly one array element. The expression simplifies to:

SELECT x[1]
FROM   regexp_matches('i1 into o2, and g1 into o17', '[gio][0-9]{1,}', 'g') AS x;

Other Solutions

SELECT unnest(x)  -- also works for cases with multiple elements per result row

SELECT trim(x::text, '{}') -- corner cases with results containing `{}`

SELECT rtrim(ltrim(x::text, '{'), '}') AS x1 -- fewer corner cases

If the pattern can or shall not match more than one time per input value, also drop the optional parameter 'g'.

And if the function shall always return exactly one row, consider the subtly different variant regexp_match() introduced with Postgres 10.

In Postgres 10 or later it's also prudent to suggest the set-returning function (SRF) regexp_matches() in the SELECT list directly (like Rick provided) since behavior of multiple SRFs in the SELECT list has finally been sanitized:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Select ... regexp_matches(....)[1] from did not work for me. But unnest did. (The single pattern matches several times, generating multiple rows.) Thanks. – Tuntable Feb 02 '16 at 09:46
  • 1
    @aberglas: The case of the OP (without capturing parentheses in the pattern) allows only *one* element in each resulting array. The fastest solution for that is the first one. If you can have more elements in the result, you need `unnest()`. I'll make that more clear. – Erwin Brandstetter Feb 02 '16 at 15:25
  • 1
    If your query is formatted `select regexp_matches(column_name, regular_expression) from table_name` then `unnest()` is the easiest way to go: `select unnest(regexp_matches(column_name, regular_expression)) from table_name` – Rick Gladwin Oct 01 '18 at 21:48
  • 1
    @RickGladwin: Yes - with reservations. See updates to this old answer. – Erwin Brandstetter Oct 02 '18 at 00:59