Why does my query with LIKE '%\_' return all rows and not just those ending in an underscore?

Question

I currently have the query running on Postgres:

SELECT * FROM addenda.users WHERE users.username LIKE '%\_' 

But rather then returning just entries ending in an underscore, I get all results back, regardless of whether it contains an underscore or not.

Running the query below returns a username which is just an underscore, so the escaping does work:

SELECT * FROM addenda.users WHERE users.username LIKE '\_' 

And running the query below returns a username which ends with a specific letter (s):

SELECT * FROM addenda.users WHERE users.username LIKE '%s' 

What am I doing wrong?

Answer 1

Is your backslash not getting through to PostgreSQL? If you're passing the string through another layer that treats the backslash as an escape character (e.g. a Java String), then that layer may be removing the backslash, and you might need to escape your backslash for that layer.

Do you have any more single character usernames? If the backslash wasn't getting through to PostgreSQL then they would also match '_'

You might be able to try the ESCAPE clause: username LIKE '%!_' ESCAPE '!'