Problems with Postgresql CASE syntax

Question

Here is my SQL query:

SELECT (CASE (elapsed_time_from_first_login IS NULL) 
                        WHEN true THEN 0 
                        ELSE elapsed_time_from_first_login END) 
FROM (
   SELECT (now()::ABSTIME::INT4 - min(AcctStartTime)::ABSTIME::INT4) 
   FROM radacct
   WHERE UserName = 'test156') AS elapsed_time_from_first_login;

When I execute the above query, I get this error:

ERROR: CASE types record and integer cannot be matched

From the error message I understand that PostgreSQL take the second select, respectively elapsed_time_from_first_login as a row, even if it will always be a single value (because of the min() function).

Question: do you have some suggestions on how to deal with this query?

Answer 1

I suppose, what you are actually trying to do should look like this:

SELECT COALESCE((SELECT now() - min(acct_start_time)
                 FROM   radacct
                 WHERE  user_name = 'test156')
              , interval '0s')

While there is an aggregate function in the top SELECT list of the subselect, it cannot return "no row". The aggregate function min() converts "no row" to NULL, and the simple form below also does the trick.

db<>fiddle here
_Oldsqlfiddle

Other problems with your query have already been pointed out. But this is the much simpler solution. It returns an interval rather than an integer.

Convert to integer

^{Simplified with input from artaxerxe.}
Simple form does the job without check for "no row":

SELECT COALESCE(EXTRACT(epoch FROM now() - min(acct_start_time))::int, 0)
FROM   radacct
WHERE  user_name = 'test156';

Details about EXTRACT(epoch FROM INTERVAL) in the manual.

Aggregate functions and NULL

If you had used the aggregate function count() instead of sum() as you had initially, the outcome would be different. count() is a special case among standard aggregate functions in that it never returns NULL. If no value (or row) is found, it returns 0 instead.

The manual on aggregate functions:

It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect, and array_agg returns null rather than an empty array when there are no input rows. The coalesce function can be used to substitute zero or an empty array for null when necessary.

Answer 2

Postgres is complaining that 0 and elapsed_time_from_first_login are not the same type.

Try this (also simplifying your select):

select
    coalesce(elapsed_time_from_first_login::INT4, 0)
from ...

Answer 3

Here is how I formatted the SQL and now is working:

SELECT coalesce(result, 0) 
     FROM (SELECT (now()::ABSTIME::INT4 - min(AcctStartTime)::ABSTIME::INT4) as result 
                FROM radacct WHERE UserName = 'test156') as elapsed_time_from_first_login;

Answer 4

The second SELECT is returning a table, named elapsed_time_from_first_login with one column and one row. You have to alias that column and use it in the CASE clause. You can't put a whole table (even if it is one column, one row only) where a value is expected.

SELECT (CASE (elapsed_time IS NULL) 
                        WHEN true THEN 0 
                        ELSE elapsed_time end) 
   FROM (SELECT (now()::ABSTIME::INT4 - min(AcctStartTime)::ABSTIME::INT4) 
                AS elapsed_time                -- column alias
         FROM radacct 
         WHERE UserName = 'test156'
        ) as elapsed_time_from_first_login;    -- table alias

and you can shorten the CASE by using the COALESCE() function (and optionally add an alias for that column to be shown in the results):

SELECT COALESCE(elapsed_time, 0) 
         AS elapsed_time 
   FROM (SELECT (now()::ABSTIME::INT4 - min(AcctStartTime)::ABSTIME::INT4) 
                AS elapsed_time            
         FROM radacct 
         WHERE UserName = 'test156'
        ) as elapsed_time_from_first_login;    -- table alias