PostgreSQL: Case insensitive string comparison

by 
select * 
where email ilike '[email protected]'

ilike is similar to like but case insensitive. For escape character use replace()

where email ilike replace(replace(replace($1, '~', '~~'), '%', '~%'), '_', '~_') escape '~'

or you could create a function to escape text; for array of text use

where email ilike any(array['[email protected]', '[email protected]'])

More Related Contents:

  1. Are PostgreSQL column names case-sensitive?
  2. Select first row in each GROUP BY group?
  3. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  4. How to use RETURNING with ON CONFLICT in PostgreSQL?
  5. PostgreSQL DISTINCT ON with different ORDER BY
  6. PostgreSQL: running count of rows for a query ‘by minute’
  7. Split comma separated column data into additional columns
  8. How to delete duplicate entries?
  9. Escaping keyword-like column names in Postgres
  10. Transpose latest rows per user to columns
  11. Delete duplicate rows (don’t delete all duplicate)
  12. GROUP BY + CASE statement
  13. subquery in FROM must have an alias
  14. Compute percents from SUM() in the same SELECT sql query
  15. SQL Between clause with strings columns
  16. Insert an image in postgresql database
  17. Perform this hours of operation query in PostgreSQL
  18. Getting date list in a range in PostgreSQL
  19. How do I delete a fixed number of rows with sorting in PostgreSQL?
  20. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  21. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  22. Postgresql Column Not Found, But Shows in Describe
  23. INSERT COMMAND :: ERROR: column “value” does not exist
  24. Return multiple columns and rows from a function PostgreSQL instead of record
  25. Postgres error updating column data
  26. SQL LIKE condition to check for integer?
  27. Counting the number of occurrences of a substring within a string in PostgreSQL
  28. Insert data and set foreign keys with Postgres
  29. Fetch records that are non zero after the decimal point in PostgreSQL
  30. How to use a ring data structure in window functions

Leave a Comment