How to escape underscores in Postgresql

postgresql escaping wildcard postgresql-9.3
Joe M picture Joe M · Jun 28, 2016 · Viewed 19.2k times · Source

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

Joe M picture Joe M · Jun 28, 2016

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'

Related questions

String literals and escape characters in postgresql

Attempting to insert an escape character into a table results in a warning. For example: create table EscapeTest (text varchar(50)); insert into EscapeTest (text) values ('This is the first part \n And this is the second'); Produces the warning: WARNING: …

Read More
Escaping backslash in Postgresql

I'm trying to write an sql function in Postgresql that will parse a file path. I want to return just the file name. I cannot get past getting an accurate text string in the function. Here is the function: Function: …

Read More
postgres dblink escape single quote

Related Link: String literals and escape characters in postgresql Here is my error: ERROR: type "e" does not exist Here is my query: SELECT * FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword', E'SELECT field_1, CASE WHEN field_2 …

Read More