Re: regexp_replace failing on 9.0.4

Rob Sargent <robjsargent(at)gmail(dot)com> writes:
> For fun I decided to install 9.2 and thought I would try my luck there.
> Here's was I saw (apologies for the wide output).

> << simple update in place>>
> update cms.segment_data
> set text = regexp_replace(text,'(^.*)ns/acres/pathology/dx/1.5(.*$)',
> E'\\1ns/acres/pathology/dx/1.6\\2')
> where id = 'c092880f-8484-4b29-b712-f3df12216701';
> UPDATE 1
> [ doesn't change the first 150 characters of the field ]

> << update in parts >>
> update cms.segment_data set text =
> regexp_replace(substring(text,1,150),
> '(^.*)ns/acres/pathology/dx/1.5(.*$)',
> E'\\1ns/acres/pathology/dx/1.6\\2') || substring(text, 151)
> where id = 'c092880f-8484-4b29-b712-f3df12216701';
> UPDATE 1
> [ does change the first 150 characters of the field ]

I'm suspicious that there is more than one match to the substring
in that field, with the extra match(es) coming beyond char 150.
The way that regexp is written, I think it would replace the last
match not the first.

regards, tom lane