How do I create an index in PostgreSQL based on lowercase only?

user_481929 0
Viewed 30961

How would I set up an index based on lower case only?

Even though the actual field contains both upper and lower case letters.

Also, can I run a query and have only the lower case index value returned?

indexing postgresql string
4 Answers

You can also use this for wildcard searches:

CREATE INDEX IF NOT EXISTS foo_table_bar_field ON foo_table(lower(username))

Query like so:

SELECT * FROM foo_table WHERE lower(username) like 'bob%'
user_591980
Related