Today I Learned

A Hashrocket project

Case insensitive `in` query in #postgres #psql

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

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.