Avoiding underscores in Postgresql

Question

When searching for underscores in Postgresql, literal use of the _ character does not work. For example, if you want to find all your tables for any columns ending in _by , for something like a change log or activity information, for example. updated_by , reviewed_by , etc., the following query almost always works:

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

Basically, it completely ignores the underscore and returns as if you were looking for LIKE '%by' . This may not be a problem in all cases, but it may be one. How to find underscores?

Answer 1

To avoid underscore, you need to use backslash. Change the sample request to the following:

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

Answer 2

Just ran into the same problem, and the only backslash didn't work. I found this documentation in the PostgreSQL community and it worked:

The correct way is to avoid underlining with a backslash. You actually need to write two backslashes in your query:

choose from foo where bar as '% \\ _ baz'

The first backslash quotes the second for the query analyzer, so what ends up inside the system is% \ _ baz, and then as the Function knows what to do with it.

So use something like this:

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

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