Back References ​

Back references identify sub-expressions that were previously matched by capturing groups within regular expressions and the replacement strings in the REGEXP_REPLACE function. Back references take the form \n, where n is the capturing group identifier, ranging from 1 to 9 (\1 to \9).

Examples ​

The following example matches all cities which have a double character in the name. The \1 refers to the previous capturing group, which holds a word character.

premdb=> select distinct(city) from team where city ~ '(\w)\1';
    city      
---------------
 Blackpool
 Cardiff
 Liverpool
 Hull
 Sheffield
 Leeds
 Middlesbrough
 Nottingham
(8 rows)

The following example uses back references in REGEXP_REPLACE and the filter expression:

premdb=> select name, regexp_replace(name, '(\w+)\W+(\w+)(?:\W+)?', '[\1] [\2]') from team where name ~ '(\w)\1\w\M';
   name     | regexp_replace  
-------------+-----------------
 Aston Villa | [Aston] [Villa]
 Blackpool   | Blackpool
 Liverpool   | Liverpool
(3 rows)