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

Answer 2

CREATE UNIQUE INDEX ux_table_field ON mytable(UPPER(field))

Answer 3

you should be able to create a function based index. (use the UPPER of the field)