Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to escape underscores in Postgresql

Tags:

postgresql

When searching for underscores in Postgresql, literal use of the character _ doesn't work. For example, if you wanted to search all your tables for any columns that ended in _by, for something like change log or activity information, e.g. updated_by, reviewed_by, etc., the following query almost works:

SELECT table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%_by' 

It basically ignores the underscore completely and returns as if you'd searched for LIKE '%by'. This may not be a problem in all cases, but it has the potential to be one. How to search for underscores?

like image 512
Joe M Avatar asked Jun 28 '16 19:06

Joe M


People also ask

How do I escape a character in postgresql?

PostgreSQL also accepts “escape” string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo' .

How do I get out of underscore in postgresql?

To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause.

What does <> mean in postgresql?

<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <> . They're exactly the same in postgresql.


2 Answers

You need to use a backslash to escape the underscore. Change the example query to the following:

SELECT table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%\_by' 
like image 149
Joe M Avatar answered Sep 29 '22 09:09

Joe M


Just ran into the same issue and the single backslash wasn't working as well. I found this documentation on the PostgreSQL community and it worked:

The correct way is to escape the underscore with a backslash. You actually have to write two backslashes in your query:

select * from foo where bar like '%\\_baz'

The first backslash quotes the second one for the query parser, so that what ends up inside the system is %\_baz, and then the LIKE function knows what to do with that.

Therefore use something like this:

SELECT table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%\\_by' 

Source Documentation: https://www.postgresql.org/message-id/10965.962991238%40sss.pgh.pa.us

like image 33
Taylor D Avatar answered Sep 29 '22 10:09

Taylor D