current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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

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

If the sub-select (with the alias elapsed_time_from_first_login) does not find a row, it will return a NULL value, but no row.

To catch this you would wrap the whole sub-select in a COALESCE statement - which is simpler than CASE for the purpose.

I overlooked the aggregate function min() at first which converts "no row" to NULL. So, as long as you have an aggregate function in the subselect, the simple form below does the trick just as well. Here is a quick demo on sqlfiddle to show the effect.

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, after comments by @artaxerxe.
Simple form does the job without check for "no row":

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

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

Aggregate functions and NULL

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

This excerpt from the manual on aggregate functions seems to cover it pretty much:

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.

share|improve this answer
    
That's the shortest one, but it does not convert time to seconds. I need it as an amount of seconds. Do you have any better idea, assuming that ABSTIME is depracated? Thanks –  artaxerxe Aug 9 '12 at 7:55
    
@artaxerxe: I assumed that an interval would be the more appropriate type to measure time to begin with. I added a variant to get the count of seconds as integer instead. –  Erwin Brandstetter Aug 9 '12 at 8:05
    
Thanks. Great answer! –  artaxerxe Aug 9 '12 at 8:29
    
@artaxerxe: I added a bit about aggregate functions and NULL that should be of interest in this context. –  Erwin Brandstetter Aug 9 '12 at 8:42
up vote 2 down vote

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 ...
share|improve this answer
    
Not working! The error is still there! But Thanks anyway for tip. –  artaxerxe Aug 9 '12 at 7:15
    
I reversed the cast - try it now –  Bohemian♦ Aug 9 '12 at 12:57
up vote 1 down vote

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;
share|improve this answer
1  
Manual says: The types abstime and reltime are lower precision types which are used internally. You are discouraged from using these types in applications; these internal types might disappear in a future release. –  LisMorski Aug 9 '12 at 7:27
up vote 1 down vote

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
share|improve this answer
    
COALESCE will catch the case where AcctStartTime IS NULL, but will fail when there is no row in table radacct with UserName = 'test156'. I provided a solution for both cases. Then again, maybe the OP wants it that way. The question does not give any indication, really. –  Erwin Brandstetter Aug 9 '12 at 8:22
1  
@ErwinBrandstetter: Actually it catches both situations. –  artaxerxe Aug 9 '12 at 8:36
2  
@artaxerxe: Ah you are right! I overlooked the aggregate function, which turns "no row" into NULL. If you remove min() from the expression, the row goes away. Quick test case on sqlfiddle to play with. –  Erwin Brandstetter Aug 9 '12 at 8:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.