Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id CE2A6632E0B for ; Thu, 1 Oct 2009 03:40:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 98220-05 for ; Thu, 1 Oct 2009 06:40:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.177]) by mail.postgresql.org (Postfix) with ESMTP id DF50A63283E for ; Thu, 1 Oct 2009 03:40:24 -0300 (ADT) Received: from webserv.wug.de ([194.25.154.138]) by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis) id 0Lxbix-1MImeD31T9-016h57; Thu, 01 Oct 2009 08:40:23 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1MtFKw-0000E3-00 for pgsql-sql@postgresql.org; Thu, 01 Oct 2009 08:40:22 +0200 Date: Thu, 1 Oct 2009 08:40:21 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Can i customize null-padding for outer joins? Message-ID: <20091001064021.GA25444@a-kretschmer.de> References: <6caee9520909302319w638c4feara4ead3402e5e00c0@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6caee9520909302319w638c4feara4ead3402e5e00c0@mail.gmail.com> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX1+w462v0J+fyj6oZEmc3T/9RAxUNnwClC6VLVw 1GTtjGTrZzpJulCzMw0LnNmlFFJUbjQlnzHfKz/wY/bXRj9akC KfYq7Zc0aDpxT+3ow84fA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.251 tagged_above=-10 required=5 tests=AWL=0.348, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200910/6 X-Sequence-Number: 33205 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)