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';

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

    RAISE EXCEPTION 'Username already exists';

(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 or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply