PostgreSQL unique indexes and string case

Question

If I create a PostgreSQL unique index on a field, is the comparison case-insensitive by default?

If not, is it possible to ask PostgreSQL to ignore string case?

Answer 1

PostgreSQL is case sensitive. To do what you want create a function index. So say

CREATE UNIQUE INDEX test_upper_idx ON mytable (UPPER(myfield)); 

That way when you use UPPER(myfield) in your query the index will be used.

See this link