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 [email protected] x1j34
2 [email protected] [email protected] 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='jo[email protected]' OR email='[email protected]') 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?