Set-returning functions are not allowed in UPDATE when using Postgres 10

Question

We have an old Flyway database update

UPDATE plays SET album = (regexp_matches(album, '^6,(?:(.+),)?tv\d+'))[1]

...that runs fine with any Postgres version from 9.2 to 9.6 but fails with latest Postgres 10. Happens even when ran directly without any JDBC.

ERROR: set-returning functions are not allowed in UPDATE

Is there a backwards incompatibility I didn't notice from version 10 release notes? Is there a workaround?

And yes there was a change in behaviour. See the item "*Change the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund)*" in the release notes: https://www.postgresql.org/docs/current/static/release-10.html (5th bullet point in the section "Migration to Version 10") — , Oct 09 '17 at 12:05
*I* would use `substring (album FROM '^6,(?:(.+),)?tv\d+')`. — Laurenz Albe, Oct 09 '17 at 13:28
@LaurenzAlbe, I needed the solution to keep compatibility with older Postgres versions so if you write an answer I'll accept it. — Anze Rehar, Oct 11 '17 at 11:39

score 9 · Accepted Answer · answered Oct 11 '17 at 12:45

9

This is untested, but should work in all PostgreSQL versions:

UPDATE plays SET album = substring (album FROM '^6,(?:(.+),)?tv\d+');

answered Oct 11 '17 at 12:45

Laurenz Albe

Scott Carey · Answer 2 · 2018-06-28T00:49:13.920

I had a more general problem where I needed the second match from a regex.

The solution was a nested subselect

SET my_column = (SELECT a.matches[2] from 
    (SELECT regexp_matches(my_column, '^(junk)?(what_i_want)$') matches) a)

Or modify the regex to return one group and apply @LaurenzAlbe 's answer:

SET my_column = substring (my_column FROM '^junk?(what_i_want)$')

There may be cases where modifying the regex is not ideal.

The original was of the form

SET my_column = regexp_matches(my_column, '^(junk)?(what_i_want)$')[2]

Where junk and what_i_want were fairly complex rexex fragments.

2 Answers2