Search code examples
postgresqlwildcard

How to escape underscores in 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?

Solution

  • 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'