0

First of all - I use JPA ORM (EclipseLink) which doesn't support ILIKE. So I am looking for solution to have case insensitive search. I did the following:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL NOT NULL,
    name VARCHAR(512) NOT NULL,
    PRIMARY KEY (id));

CREATE INDEX users_name_idx ON users USING gin (LOWER(name) gin_trgm_ops);

INSERT INTO users (name) VALUES ('User Full Name');

However, this query returns user:

SELECT * FROM users WHERE name ILIKE '%full%';

But this one doesn't:

SELECT * FROM users WHERE name LIKE '%full%';

So, how to create GIN index with LOWER in PostgreSQL?

Improve this question

1 Answer 1

Reset to default
2

I'm not sure I understand the question. because you mention GIN and insert one row and expect it to be returned with case insensitive comparison, but a wild guess - maybe you are looking for citext?..

t=# create extension citext;
CREATE EXTENSION
t=# CREATE TABLE IF NOT EXISTS users (
    id SERIAL NOT NULL,
    name citext NOT NULL,
    PRIMARY KEY (id));
CREATE TABLE
t=# INSERT INTO users (name) VALUES ('User Full Name');
INSERT 0 1
t=# SELECT * FROM users WHERE name LIKE '%full%';
 id |      name
----+----------------
  1 | User Full Name
(1 row)

update

expression based index requires expression in query

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

7 Comments

Thank you for your idea. However, I don't need to solve problem with users table, I used users table for example. In real DB I have about 50 tables with varchar.
varchar with limit?.. not the popular choice. why not text?.. you can alter table alter column type citext for those tables
Yes, varchar with limits (64, 128, 256 and 512). Why do you say it is not popular choice?
However, anyway I want to get answer to my question. I don't like when something works not as I expect because it means I do something wrong and don't understand something.
updated the answer. basically if you want the index to shorten path to result, you have to exactly repeat expression you used to create the index. and if you create an index with function, it does not mean db will itself perform that function even if you don't use it in query (If I understand your intention correctly)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.