Question 277891 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres select based on true condition from another select

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?

Tags:

2

Answers


  1. 0

    You can do:

    SELECT
  u.*, 
  password_hash = crypt('password123', password_hash) as valid
FROM users u
WHERE email='[email protected]';
    Login or Signup to reply.
  2. 0

    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]';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search