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)

Answer 4

Another approach would be to make you column data type a citext (case-insensitive text).

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );

SELECT * FROM users WHERE nick = 'Larry';

This way tou do not need to calll the lower function on the index creation.

CREATE UNIQUE INDEX index_users_on_nick ON users (nick);

Usefull links:

• PostgreSQL citext
• shuber Case insensitive UNIQUE constraints in Postgres