PostgreSQL: Unique usernames

A common requirement for web applications is to allow people to register with a unique username. A naive solution to this in PostgreSQL is to simply slap a UNIQUE constraint on the username column and call it a day.

CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL UNIQUE
);

PostgreSQL will give us a unique constraint error when someone tries to register with a username that has been taken. Problem solved!

INSERT INTO users (username) VALUES ('bear');
INSERT INTO users (username) VALUES ('Bear');
INSERT INTO users (username) VALUES ('BEAR');

Huh, these all insert without issues. No unique constraint errors?

Indeed, we need unique, case-insensitive usernames. But that's easy enough, we'll lowercase the usernames before inserting.

DELETE FROM users;
INSERT INTO users (username) VALUES (lower('bear'));
INSERT INTO users (username) VALUES (lower('Bear'));
ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (username)=(bear) already exists.

Et voila, unique constraint error. Except... Now everyone will always have lowercased usernames on our website. That's not ideal. Unless that's your kind of aesthetic.

We need unique, case-insensitive usernames that also don't lose their casing. Alright then, let's create a unique index on the lowercased username instead. Best of both worlds!

DROP TABLE users;

CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL /*UNIQUE*/
);

CREATE UNIQUE INDEX users_username_key ON users (lower(username));

And now we're done, right?

Not just yet. We have our unique constraint now, except we've changed the underlying index for the username column. Which means that when it comes time to actually use that column in queries, we must pay attention to always lowercase it as well.

Otherwise we're creating an index and then never actually using it.

EXPLAIN (COSTS off) SELECT * FROM users WHERE username = 'bear';

             QUERY PLAN              
-------------------------------------
 Seq Scan on users
   Filter: (username = 'bear'::text)

Oof, a sequential scan even though we have an index available. Let's fix that.

EXPLAIN (COSTS off) SELECT * FROM users WHERE lower(username) = lower('bear');

                   QUERY PLAN                   
------------------------------------------------
 Index Scan using users_username_key on users
   Index Cond: (lower(username) = 'bear'::text)

There we go, an index scan. That's much better.

And yes, both sides of the comparison need to be lowercased. Otherwise we'll end up comparing a case-insensitive string to a case-sensitive one, for example as lower('Bear') = 'Bear'. Which will never return any results because it is always false.

Let's check that inserting still gives us our unique constraint error.

INSERT INTO users (username) VALUES ('bear');
INSERT INTO users (username) VALUES ('Bear');
ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (lower(username))=(bear) already exists.

Perfect! And now we're done.

Now we can store usernames with their casing intact while at the same preventing case-insensitive duplicate usernames from existing. We've achieved our goal.

But wait, there's more! We can also achieve the same result by using the CITEXT type instead.

DROP TABLE users;

CREATE EXTENSION citext;

CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username CITEXT NOT NULL UNIQUE
);

We still get our unique constraint error when inserting the same username.

INSERT INTO users (username) VALUES ('bear');
INSERT INTO users (username) VALUES ('Bear');
ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (username)=(Bear) already exists.

And our index is used without having to call lower() ourselves.

EXPLAIN (COSTS off) SELECT * FROM users WHERE username = 'bear';

                  QUERY PLAN                  
----------------------------------------------
 Index Scan using users_username_key on users
   Index Cond: (username = 'bear'::citext)

Very nice!

Which one to use will depend on what approach you like. As well as whether any of the CITEXT type limitations apply to you or not. But, now we have unique usernames in PostgreSQL.