coalesce seems to give strange results

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

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:

Richard Yen <richyen@iparadigms.com> wrote:

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

That's right, /but/ if you use a sub-select, you can achieve
something similar:

| tim=# SELECT COALESCE((SELECT ROUND(EXTRACT(epoch FROM now() - query_start))
| tim(# FROM pg_stat_activity
| tim(# WHERE current_query = '<IDLE> in transaction'),
| tim(# 0);
| coalesce
| ----------
| 0
| (1 Zeile)

| tim=#

sorry for the spam.
[...]

That wasn't spam :-).

Tim