hi,

i try to execute an update containing a regex. im using postgres 8.2 and hibernate 3.3.1.ga

the correct syntax for a postgres regex containing backreferences would be:

update foo set bar = 1 where foobar ~* E'(foo)\\1\\1'
the "E" means "escape"

and no, the like - keyword wouldnt be enough for complicated regex.

it may look strange, but its the only way i could get the backreferences work correctly.

now using a hibernate native query im having problems to use the "E".

solution 1 would be to concatenate the whole sql, but since "{" is a reserved character this wont work for some regex..

solution 2 would be using named parameters, but this wont work because i cant use the escape character "E":
update foo set bar = 1 where foobar ~* E:expr
obviously wont work..

so what should i do?