databasepostgresqlindexing

PostgreSQL unique indexes and string case

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?

Solution

  • 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