SQL - How to escape parentheses in PostgreSQL

Question

I have the following sql command, i need to escape parentheses in PostgreSQL, how can i do that?

SELECT rua 
  FROM logradouros 
 WHERE rua ~* 'Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)' 
 LIMIT 100;

Answer 1

Use backslash to escape parentheses. Note that if standard_conforming_strings parameter is set to off (which is default behaviour up to PostgreSQL 9.1) you need use two backslashes \\.

Generally there are three approaches how to escape parentheses:

• ordinary 'pattern' syntax, which is dependent on standard_conforming_strings setting
• escape string constant e.g. E'pattern'
• dollar-quoted string constants $$pattern$$ or $sometext$pattern$sometext$

The first one is standard SQL (especially with standard_conforming_strings), others are PostgreSQL extensions. Choose whatever method you like.

Here you have some examples:

SET standard_conforming_strings = 0;

SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~* 
'Antonio De Sant\'Ana Galvao Av Frei \\(Av 01 Parte A\\)'

SET standard_conforming_strings = 1;

SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~* 
'Antonio De Sant''Ana Galvao Av Frei \(Av 01 Parte A\)'

SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~* 
E'Antonio De Sant\'Ana Galvao Av Frei \\(Av 01 Parte A\\)'

SELECT
'Antonio De Sant''Ana Galvao Av Frei (Av 01 Parte A)'
~* 
$$Antonio De Sant'Ana Galvao Av Frei \(Av 01 Parte A\)$$

SET standard_conforming_strings = default;

Answer 2

Generally speaking, dollar quoting is your best bet. Observe:

SELECT $STR$Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)$STR$;
-----------------------------------------------------
 Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)
(1 row)