How Can I Write a Postgres "if" Based on a SELECT?

All we need is an easy explanation of the problem, so here it is.

If I have a function in PostgreSQL, I can validate its arguments (eg. a username argument) like so:

IF LENGTH(username) < 4 THEN
    RAISE EXCEPTION 'Usernames must contain at least four characters';
END IF;

What I can’t figure out is how to do the same thing with the results of a SELECT query, ie. something like:

IF LENGTH(SELECT * FROM USERS WHERE username=username) > 1 THEN
    RAISE EXCEPTION 'Username already exists';
END IF;

(And yes, I know PostgreSQL will give me an error without that line, but I want to have a friendlier error message.)

Is something like that possible in (Postgre)SQL, and if so can someone point me at the syntax (or an example of it) that I could look at?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, that is possible, but the subquery needs an extra pair of parentheses:

length((SELECT col FROM users WHERE ...))

One pair of parentheses is for the subquery, one for the function call.

The more readable way to write this is

(SELECT length(col) FROM users WHERE ...)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply