Re: coalesce seems to give strange results

Ah, I see what you mean.  If there's no rows to return, then there's no coalesce-ing to do...

sorry for the spam.

--Richard



On Jul 14, 2010, at 5:12 PM, Thom Brown wrote:

> On 15 July 2010 00:52, Richard Yen <richyen@iparadigms.com> wrote:
>> Hi everyone,
>>
>> Looks like I'm encountering some quirks with coalesce()...
>>
>>> postgres=# select coalesce(null,0);
>>>  coalesce
>>> ----------
>>>         0
>>> (1 row)
>>>
>>> postgres=# SELECT COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) FROM pg_stat_activity WHERE
current_query= '<IDLE> in transaction'; 
>>>  coalesce
>>> ----------
>>> (0 rows)
>>>
>>> postgres=# select version();
>>>                                                       version
>>> -------------------------------------------------------------------------------------------------------------------
>>>  PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 64-bit
>>> (1 row)
>>
>> Any ideas?
>> --Richard
>> --
>
>
> I don't see what you mean.  The query can't return any rows because
> none match the criterion specified.  If you'd normally get 0 rows if
> using SELECT *, then adding in coalesce isn't going to force a result
> as there's nothing to select against.
>
> postgres=# SELECT COALESCE(NULL,0) FROM pg_stat_activity WHERE
> current_query = '<IDLE> in transaction'; coalesce
> ----------
> (0 rows)
>
> postgres=# select coalesce(null,0) from pg_database where 1 = 2;
> coalesce
> ----------
> (0 rows)
>
> Thom