How to create GIN index with LOWER in PostgreSQL?

Question

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?

Answer 1

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