Thread: coalesce seems to give strange results
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
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: > 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
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