Postgres select based on true condition from another select-CodePudding

I'm a bit new to postgres and quite rusty with SQL.

I have a users table with password_hash column which stores a hashed password through pgcrypto.

id  |    name    |      email      |           password_hash            |
---- ------------ ----------------- ------------------------------------ 
  1 | john doe   | [email protected]| $1$47i7aL0t$bUUZWSNhu.KBuFBlwYN3x1 |

Verifying whether a user's password is valid can be achieved with this select:

SELECT password_hash = crypt('password123', password_hash) FROM users where email='[email protected]';

This returns t or f depending on whether the password matches.

As part of my authorization logic, I need to return the user's row if the password is valid, but I would like to achieve this in a single query instead of first doing the SELECT on the password_hash and then doing another SELECT to retrieve the user's row. What would be the best way to construct a query to do this?

CodePudding user response：

You can move the password check down to WHERE section, then make sure both the email AND password are correct:

SELECT users.* FROM users
WHERE password_hash=crypt('pass123',password_hash) 
AND email='[email protected]';

CodePudding user response：

You can do:

SELECT
  u.*, 
  password_hash = crypt('password123', password_hash) as valid
FROM users u
WHERE email='[email protected]';