pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan i customize null-padding for outer joins?
3+ messages / 3 participants
[nested] [flat]
* Can i customize null-padding for outer joins?
@ 2009-10-01 06:19 Shruthi A <shruthi.iisc@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Shruthi A @ 2009-10-01 06:19 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a query where I full-outer-join 2 tables, and all the columns other
than the join column are numerical columns. For my further calculations i
need to pad the unmatched tuples with 0 (zero) instead of NULL so that I can
perform meaningful mathematical calculations on them. Is this currently
possible?
Thanks a ton!
Shruthi
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can i customize null-padding for outer joins?
@ 2009-10-01 06:22 Scott Marlowe <scott.marlowe@gmail.com>
parent: Shruthi A <shruthi.iisc@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2009-10-01 06:22 UTC (permalink / raw)
To: Shruthi A <shruthi.iisc@gmail.com>; +Cc: pgsql-sql
On Thu, Oct 1, 2009 at 12:19 AM, Shruthi A <shruthi.iisc@gmail.com> wrote:
> Hello,
>
> I have a query where I full-outer-join 2 tables, and all the columns other
> than the join column are numerical columns. For my further calculations i
> need to pad the unmatched tuples with 0 (zero) instead of NULL so that I can
> perform meaningful mathematical calculations on them. Is this currently
> possible?
something like
select a.x, coalesce(b.y,0) from a left join b yada...
work?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can i customize null-padding for outer joins?
@ 2009-10-01 06:40 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Shruthi A <shruthi.iisc@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: A. Kretschmer @ 2009-10-01 06:40 UTC (permalink / raw)
To: pgsql-sql
In response to Shruthi A :
> Hello,
>
> I have a query where I full-outer-join 2 tables, and all the columns other than
> the join column are numerical columns. For my further calculations i need to
> pad the unmatched tuples with 0 (zero) instead of NULL so that I can perform
> meaningful mathematical calculations on them. Is this currently possible?
You can use COALESCE() in your numerical calculation, for instance:
test=*# create table foo (n numeric);
CREATE TABLE
test=*# insert into foo values (1),(NULL),(NULL);
INSERT 0 3
test=*# select avg(n) from foo;
avg
------------------------
1.00000000000000000000
(1 row)
test=*# select avg(coalesce(n,0)) from foo;
avg
------------------------
0.33333333333333333333
(1 row)
Or use coalesce in your join-statement:
select coalesce(column,0) ...
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-10-01 06:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-01 06:19 Can i customize null-padding for outer joins? Shruthi A <shruthi.iisc@gmail.com>
2009-10-01 06:22 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-10-01 06:40 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox