Re: coalesce seems to give strange results

On 15 July 2010 00:52, Richard Yen <richyen(at)iparadigms(dot)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