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?
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' .
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.
<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <> . They're exactly the same in postgresql.
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'
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With