I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. However, when matching their input against usernames and email addresses I want to ensure that a username match gets priority over an email match.

For example, in the following contrived instance:

id | username        | email address    | pw_hash
1    john_doe          john@domain.com    x1j34
2    john@domain.com   john@domain.com    x1j34

if both users enter their username, I want to guarantee that user 2 gets logged in.

If I use:

SELECT * FROM member WHERE (username='john@domain.com' OR email='john@domain.com') AND pw_hash='x1j34';

this matches both users but provide no guarantee on order.

Is there a way to have to conditions, and only check the second condition if the first isn't satisfied? Can this be done in a single query?


You have two conditions to check during the authentication:

  1. There is a user with matching username; do not need to match by email.

  2. There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com' 
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.

| improve this answer | |
  • Yes, that would be an issue. I'm modeling off a prominent site that allows duplicate emails. I've found that in their case, if two users share an email and pass, logging in with email fails without an explanation. The user must use his username in that case. Certainly not ideal, but perhaps this is enough of an edge case (only applying to users who are playing around with the system) that it's sufficient. – Lorenz Forvang Jun 9 '14 at 8:21
  • @LorenzForvang Surely, if you are okay with these unlikely but possible issues, it's a fine scheme. By the way, the query is what you needed, right? – hellodanylo Jun 9 '14 at 8:24
  • The suggested query returns both users, but I'd like only User 2 returned. – Lorenz Forvang Jun 9 '14 at 8:26
  • @LorenzForvang Yes, I haven't looked into the query in depth at first. Check the updated one. – hellodanylo Jun 9 '14 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.