escaping bracket in postgresql query

Question

I am trying to escape a bracket in a pattern matching expression for PostgreSQL 8.2

The clause looks something like:

WHERE field SIMILAR TO '%UPC=\[ R%%(\mLE)%'

but I keep getting:

ERROR:  invalid regular expression: brackets [] not balanced

Answer 1

Try this:

select '%UPC=\[ R%%(\mLE)%';
WARNING:  nonstandard use of escape in a string literal
LINE 1: select '%UPC=\[ R%%(\mLE)%';
               ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
     ?column?     
------------------
 %UPC=[ R%%(mLE)%
(1 row)

You need to set Postgres in standard conforming strings mode instead of backward compatible mode.

set standard_conforming_strings=1;
select '%UPC=\[ R%%(\mLE)%';
      ?column?      
--------------------
 %UPC=\[ R%%(\mLE)%
(1 row)

Or you need to use escape string syntax which works regardless of mode:

set standard_conforming_strings=1;
select E'%UPC=\\[ R%%(\\mLE)%';
      ?column?      
--------------------
 %UPC=\[ R%%(\mLE)%
(1 row)

set standard_conforming_strings=0;
select E'%UPC=\\[ R%%(\\mLE)%';
      ?column?      
--------------------
 %UPC=\[ R%%(\mLE)%
(1 row)

You can set this setting in postgresql.conf for all databases, using alter database for single database, using alter user for single user or group of users or using set for current connection.