CODEWITHSUNDEEP
postgresql
John
John

Reputation: 426

Sorting with UPPER and LOWER in Postgres

I'm pretty new to postgres, and wanted to know how to sort my table using the upper(or lower) functions to make a case-insensitive search. I know I could do something along the lines of: 

where b.name ~* '%Example%'

But that method makes my query a bit slow. So I wanted to try the following below.

Assume that table b is like this:

order | name
1     | Example
2     | example
3     | EXAMPLE
4     | ExAmPlE

Whenever I use the query:

set schema 'schem';
    select  UPPER(b.name),
        from b
        where b.name like UPPER('%Example%');

What it comes down to is the query itself. Whenever the where clause is:

where b.name like UPPER('%Example%');

Nothing displays.

where b.name like UPPER('%E%');

Example, EXAMPLE, and ExAmPlE show in all upper-case.

where b.name like UPPER('%EXAMPLE%');

Only EXAMPLE shows.

Maybe I'm not understanding postgres right, but does the upper function only show its parameter's data in all caps? What I thought was happening is that my query will take all of the examples, force them to all upper-case, and then the where clause will also be forced to upper-case, and therefore whenever I used any of the where clauses mentioned above, each of those queries would spit everything in table b.

Am I forced to having to use the format where b.name ~* '%Example%' or am I just misunderstanding this?

Upvotes: 1

Views: 3683

Answers (1)

hruske
hruske

Reputation: 2243

Postgresql strings are case sensitive and you need to use upper on columns if you want to match your upper() query.

If you want to search case insensitive, you can also use ILIKE instead of LIKE.

If you for some reason need to store case insensitive text, there is an extension citext.

There is not much difference between name LIKE '%argh%' and name ~* 'argh', these are basically the same the way we use them in this example. The ~* operator is however much more powerful as it is a case insensitive regular expression match. (Case sensitive is ~).

If you want to have a fast query where you filter on condition, you might want to reconsider if you can change condition in such a way to match the field at beginning. Then you can use normal btree index with upper:

create index b_name_idx on b (upper(name));

This is called functional index (because you use function), and in order for postgresql to use it you must also use upper on your field:

select * from b where upper(b.name) like 'argh%';

However, if you must check for containment, if searched string can be somewhere in the middle, you might want to use pg_trgm extension.

create extension pg_trgm;
create index b_name_trgmidx on b using gin (name gin_trgm_ops);

Then these both will be able to use index:

select * from b where b.name like '%argh%';
select * from b where b.name ~* 'argh';

Upvotes: 3

Related Questions