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?
Add a comment
|
4 Answers
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.
2 Comments
Kuberchaun
@szeryf Better in what way? Anything to prove that it's better?
Emaborsa
These solutions do not get triggered when we have an
INSERT.... ON CONFLICT (myfield)...Another approach would be to change your column's data type to citext (case-insensitive text).
The
citextmodule provides a case-insensitive character string type,citext. Essentially, it internally callslowerwhen comparing values. Otherwise, it behaves almost exactly liketext.
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 you do not need to call the lower function on the index creation.
CREATE UNIQUE INDEX index_users_on_nick ON users (nick);
Useful links:
1 Comment
caiohamamura
This is the right solution, that is exactly the purpose
citext was created for, the OP should change this to be the accepted answer, although this probably wasn't implemented when the question was first posted.CREATE UNIQUE INDEX ux_table_field ON mytable(UPPER(field))
Comments
you should be able to create a function based index. (use the UPPER of the field)
