Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E043E1337B99 for ; Mon, 10 Jan 2011 22:45:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 60602-10 for ; Tue, 11 Jan 2011 02:45:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 81D6A1337B9D for ; Mon, 10 Jan 2011 22:45:51 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p0B2joHb005648; Mon, 10 Jan 2011 21:45:50 -0500 (EST) To: gvim cc: pgsql sql Subject: Re: Find NOT NULLs in a group of 20 columns In-reply-to: <4D2BBCF4.8070905@gmail.com> References: <4D2BBCF4.8070905@gmail.com> Comments: In-reply-to gvim message dated "Tue, 11 Jan 2011 02:14:12 +0000" Date: Mon, 10 Jan 2011 21:45:50 -0500 Message-ID: <5647.1294713950@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/23 X-Sequence-Number: 34970 gvim writes: > I'm struggling to find anything other than a complex stored proc to do this: > Out of a group of 20 columns return the rows in which: > 1. Only 1 of the 20 columns IS NOT NULL > 2. More than 1 of the 20 columns IS NOT NULL > Any ideas? Cast to int and add up? SELECT ... WHERE ((col1 is not null)::int + (col2 is not null)::int + ... (col20 is not null)::int) = 1 -- or > 1 regards, tom lane