SQL - How to escape parentheses in PostgreSQL

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

SEL开发者_如何学编程ECT rua 
  FROM logradouros 
 WHERE rua ~* 'Antonio De Sant\'Ana Galvao Av Frei (Av 01 Parte A)' 
 LIMIT 100;

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;

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)

Try this:

SELECT rua from logradouros where rua ~* E'Antonio De Sant\'Ana Galvao Av Frei \(Av 01 Parte A\)' limit 100;

Reference: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html

Although every other response helps, I just think that using quoting functions is the 'proper' way of doing it.

from docs:

quote_literal(string text) text Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 39-1. quote_literal(E'O\'Reilly') 'O''Reilly'

just use 2 ' '

select * from games where name = 'Ahmed''s Salah'

backslash(es) didn't work for me

As for the regex, You gotta use double backslashes right in front of the parentheses!!

E.g.:

select regexp_matches('FOREIGN KEY (user_transaction_id) REFERENCES landing.user_transactions(id)', E'FOREIGN KEY \\((.*)\\) REFERENCES (.*)\\((.*)\\)', 'g');

Result:

"{user_transaction_id,landing.user_transactions,id}"