I basically need to replace a duplicate word in a certain condition, but the word appears a lot of times throughout the string. So how can I transform this:
') test test test'
Into this:
') test successful successful'
I tried using this and some other variants of it, but none worked:
SELECT regexp_replace(') test test', '!~*[)] test|test)', 'succesful','gi')
What you're looking for normally requires a regex engine that supports variable-length lookbehind since only occurrences of test with at least one occurrence of test behind them are to be replaced.
To work around the lack of such a support in PostgreSQL, you can replace the first occurrence of test with a dummy text that is highly unlikely to ever occur in any given input, then proceed to replace the rest of the occurrences of test with successful, and finally replace the dummy text back to test:
SELECT replace(replace(regexp_replace(') test test test', ' test', '!!dummy!!'), ' test', ' successful'), '!!dummy!!', ' test')