Case insensitive `in` query in #postgres #psql

April 23, 2018

  • sql

Originally posted by me on Hashrocket TIL

If you have a list of strings and you want to query a column to get all the matching records, but you do not care about the casing, Postgres offers a cool and easy way of doing that with the citext extension.

Given this table:

id | company_name
 1 | Abibas
 2 | Nykey
 3 | Pumar

We want to match the following:

select company_name
from vendors 
where company_name in ('Abibas', 'NyKey', 'PUMAr');

First you will have to make sure you have the citext extension created if you haven’t already:

create extension citext;

Then you can cast the searched field to citext:

select company_name
from vendors 
where company_name::citext in ('Abibas', 'NyKey', 'PUMAr');

h/t joshbranchaud for helping me find this

Written by Dorian Karter, a Sr. Software Engineer
Github | Twitter

© 2021, Dorian Karter