Sorting with UPPER and LOWER in Postgres

Question

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?

You have to use the function on both sides as: where upper(b.name) like UPPER('%ExAmPle%') — Jorge Campos
– Jorge Campos, Commented Feb 29, 2016 at 16:18

hruske · Accepted Answer · 2016-02-29 16:33:47Z

3

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';

edited Feb 29, 2016 at 16:33

answered Feb 29, 2016 at 16:28

hruske

2,24319 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jorge Campos Over a year ago

There is not much difference between name LIKE '%argh%' and name ~* 'argh', these are basically the same. You are wrong here. Yes there is a HUGE difference, since ~* operator uses regular expression and it is way slower than the LIKE operator. See the [9.7.3. POSIX Regular Expressions] (postgresql.org/docs/9.3/static/functions-matching.html) . Everything else is fine on your answer though.

hruske Over a year ago

Updated, +1 for noticing.

Collectives™ on Stack Overflow

Sorting with UPPER and LOWER in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related