Обсуждение: Case insensitive query on existing data

Поиск

Список

Период

Сортировка

Case insensitive query on existing data

От

Saurav Sarkar

Дата:

13 августа 2020 г., 11:40:53

Hi All,

We use PostgreSQL JSONB storage.

One of the keys in the JSON will be always have a "name" key.

We want to perform case insensitive query on the name key.

Our application is already live so we have some data and have existing indexes on the name key.

I understand that one of the ways is to create index on the lower(name) and use the lower function. And then to recreate the indexes on the name key.

Is there any other way through which this can be achieved ? May be an option to not to recreate indexes.

Thanks and Best Regards,

Saurav

Re: Case insensitive query on existing data

От

Michael Lewis

Дата:

13 августа 2020 г., 17:15:02

create index concurrently lower( jsonb->>'name' ), drop old_index concurrently, ensure that where/on/group by conditions use lower( jsonb->>'name' ), then take lunch.

What's your concern with this process?

Re: Case insensitive query on existing data

От

Saurav Sarkar

Дата:

13 августа 2020 г., 18:46:50

Thanks a lot Michael . Concurrent index building solves my problem of building index without locking.

Best Regards,

Saurav

On Thu, Aug 13, 2020 at 10:45 PM Michael Lewis <mlewis@entrata.com> wrote:

create index concurrently lower( jsonb->>'name' ), drop old_index concurrently, ensure that where/on/group by conditions use lower( jsonb->>'name' ), then take lunch.

What's your concern with this process?

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Case insensitive query on existing data

Case insensitive query on existing data

Re: Case insensitive query on existing data

Re: Case insensitive query on existing data