Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2SJq2U69382 for ; Wed, 28 Mar 2001 14:52:02 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f2SJpdw19363; Wed, 28 Mar 2001 14:51:39 -0500 (EST) To: Dirk Lutzebaeck cc: Michael Fork , pgsql-sql@postgresql.org Subject: Re: counting distinct rows on more than one column In-reply-to: <15042.15431.15325.327757@ampato.core.aeccom.com> References: <15041.65308.150764.131312@ampato.core.aeccom.com> <15042.15431.15325.327757@ampato.core.aeccom.com> Comments: In-reply-to Dirk Lutzebaeck message dated "Wed, 28 Mar 2001 21:32:23 +0200" Date: Wed, 28 Mar 2001 14:51:39 -0500 Message-ID: <19360.985809099@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/588 X-Sequence-Number: 2258 Dirk Lutzebaeck writes: > Michael Fork writes: >>> In 7.0.3, I believe the following would work: >>> >>> SELECT count(distinct(a || b)) FROM t; > Great, this works! I don't quite get it why... Michael really should not have proposed that solution without mentioning its limitations: it's not actually counting distinct values of the column pair a,b, but only of their textual concatenation. For example a = 'xy' and b = 'z' will look the same as a = 'x' and b = 'yz'. If there is some character you never use in column A, say '|', you could do count(distinct(a || '|' || b)) with some safety, but this strikes me as still a pretty fragile approach. regards, tom lane