How to escape underscores in Postgresql

Question

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?

Answer 1

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'

Answer 2

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

Answer 3

Just a point of pedantry on one aspect of your question:

It basically ignores the underscore completely and returns as if you'd searched for LIKE '%by'.

This isn't correct. What's happening here is that underscore is another wildcard character supported by postgres' pattern matching. From: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE

An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

i.e if you've ever used DOS wildcards, the underscore is analgous to the ? DOS wildcard, matching any character, but exactly one character, whereas the % in postgres matches 0 or more characters - i.e % matches any string including an empty string, but _ matches any string which is exactly one character long.

Thus, WHERE column_name LIKE '%_by' isn't quite the same as WHERE column_name like '%by', since the string by does not match the first but does match the second (it has 0 characters before the by and therefore does not match the underscore wildcard)