CODEWITHSUNDEEP
postgresqlindexingconstraintscase-insensitive
user8450148
user8450148

Reputation:

PSQL case insensitive INDEX lower() doesn't work

Currently, I am working on a making data in a column, "filename", to be case insensitive when they are searched or compared. In the files DB, I have files with names SAMPLE.txt and sAMple.Txt. ... And I want to get all this files with the same name when I execute

SELECT * from files where filename='sample.txt'

So, I made an index by this command...

CREATE INDEX files_lower_fnames_idx ON files ((lower(filename)));

It was successfully created. But upon executing...

SELECT * from files where filename='sample.txt'

it still returned 0 rows.

Am I missing something? Thank you for your help.

Upvotes: 1

Views: 1604

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

it still returned 0 rows.

Creating an index has no effect on the outcome of a query, only the performance of the query. If it returned 0 rows before index creation, it should after too.

I can't say for sure why it's returning 0 rows, since you haven't shown the data in the table. But if it's as you describe then

SELECT * from files where filename='sample.txt'

would not match it. You need either a citext column, or to do a case insensitive query like

SELECT * from files where lower(filename)='sample.txt'

This query should be able to use the index you created to run faster.

Upvotes: 1

Related Questions