Database Reference
In-Depth Information
Case-insensitive text - citext
This data type is very useful for working with queries where the data is case insensitive.
You can use this feature with an implementation to facilitate your searches, such as search-
ing for products on an e-commerce website.
For you to understand, the data type
text
is generally used, but it is case sensitive. For ex-
ample, suppose you have a table with the column username and an index to avoid duplicate
usernames:
$ heroku pg:psql -app your-app-name
CREATE TABLE students (username text NOT NULL);
CREATE UNIQUE INDEX unique_username_on_students ON students
(username);
Next, you'll insert some student names:
INSERT INTO students (username) VALUES ('patrick');
INSERT INTO students (username) VALUES ('PATRICK');
In the preceding example, two records were created; as the data type
text
is case sensit-
ive, so the values '
patrick
' and '
PATRICK
' are different. This is the problem that exten-
sion
citext
resolves, it allows you to define case insensitive data.
After that, you'll switch to use the
citext
extension:
CREATE EXTENSION citext;
TRUNCATE TABLE students;
ALTER TABLE students ALTER COLUMN username TYPE citext;
DROP INDEX unique_username_on_students;
CREATE UNIQUE INDEX unique_username_on_students ON students
(username);
Finally, if you try to repeat the inserts, you'll have an error:
INSERT INTO students (username) VALUES ('patrick');
INSERT INTO students (username) VALUES ('PATRICK');
# ERROR: duplicate key value violates unique constraint
