17

I'm trying move hash cheking from server app to PostgreSQL. In other words I'm need calling a query to PGSQL, which compare string from query with string from field and return result of equal comparison as bool, but I don't know how do this without procedures on clear SQL.

upd: I have table users with field password(currently text, in the future - bytea). I want write sometihng like 

select * from values ('WrittenPassword' = users.password) where username = 'someuser' ,

which must return true or false as result of equal comparison.

Arman Hayots
  • 2,459
  • 6
  • 29
  • 53

1 Answers1

34

You can use a CASE statement to return certain values based on a condition:

SELECT 
    (CASE WHEN password = 'WrittenPassword' THEN 1 ELSE 0 END) AS is_equal
FROM
    users
WHERE
    username = 'someuser'
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • 2
    What does the query return when the username does not exist, Okay? There is nothing to compare, Okay? please look up "three valued logic" Okay? – wildplasser Jun 10 '12 at 23:52
  • 2
    @wildplasser, if the username does not exist, it simply won't return any rows because the WHERE clause would filter it out. Otherwise, if the user does exist, then the password can only be equal to or not equal to the input string, and so you have to get either 0 or 1. – Zane Bien Jun 11 '12 at 00:03
  • 2
    I know (and I think you know), but the result depends on how the caller handles the three valued logic (NULL := "no rows returned") The OP does not understand this, and is resorting to dude-speech. When three valued logic is present, we'll have to formulate even more precise. – wildplasser Jun 11 '12 at 00:08
  • 1
    If I got null, it simply doesn't change result flag in my code from false to true(I'm using bool.TryParse() instead of Parse()). Null can't be correct value of password, exactly. – Arman Hayots Jun 11 '12 at 14:20
  • You can always add logic for the NULL such as IF (col IS NULL) with an OR or AND as appropriate. Obviously you could do likelwise for IF (col IS NOT NULL) – JosephDoggie Mar 15 '21 at 20:22