Postgres - using select statement inside CASE WHEN

Question

Inside case when condition I am executing select statement & if returns anything then I need to get it's value but I am getting error

ERROR:  missing FROM-clause entry for table "us"

query is..

SELECT u.user_id,
       CASE
           WHEN
                  (SELECT us.attr_value
                   FROM user_setting us
                   WHERE us.user_id = u.user_id) IS NOT NULL THEN us.attr_value
           ELSE
                  (SELECT gus.attr_value
                   FROM global_user_setting gus
                   WHERE gus.attr_key='key')
       END
FROM user u
WHERE u.user_id IN (1,
                          2,3)

Error comes at IS NOT NULL THEN us.attr_value I understood the issue but couldn't find how to get that value outside select statement?

Answer 1

Try:

COALESCE((SELECT us.attr_value
               FROM user_setting us
               WHERE us.user_id = u.user_id),
         (SELECT us.attr_value
               FROM global_user_setting gs
               WHERE gus.attr_key='key'))

instead. The reason for the problem is, that the binding of the es alias is not visible outside of the sub-select (as it is used in a "scalar" context). The whole subselect is basically a single expression, which will yield a single value.

Another (IMHO better) approach would be to left-join on the enrollment_settings table:

SELECT u.user_id,
       COALESCE(us.attr_value, (SELECT gus.attr_value
                                FROM global_user_setting gs
                                WHERE gus.attr_key='key'))
FROM user u LEFT JOIN user_settings es ON us.user_id = u.user_id
WHERE u.user_id IN (1, 2, 3)

I assume here, that this join would yield at most a single row per row of user.