Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1joSvh-0006ST-MD for pgsql-bugs@arkaria.postgresql.org; Thu, 25 Jun 2020 14:32:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1joSvg-0006pa-JJ for pgsql-bugs@arkaria.postgresql.org; Thu, 25 Jun 2020 14:32:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1joSvg-0006p6-DZ for pgsql-bugs@lists.postgresql.org; Thu, 25 Jun 2020 14:32:40 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1joSve-0006JQ-8F for pgsql-bugs@lists.postgresql.org; Thu, 25 Jun 2020 14:32:39 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 05PEWYJY2270141; Thu, 25 Jun 2020 10:32:34 -0400 From: Tom Lane To: Daniel Gustafsson cc: aceonline@gmx.de, PostgreSQL mailing lists Subject: Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error In-reply-to: <5CF2CF6C-8951-4CA1-980C-94F369B7EE86@yesql.se> References: <16510-755ed3d371542beb@postgresql.org> <5CF2CF6C-8951-4CA1-980C-94F369B7EE86@yesql.se> Comments: In-reply-to Daniel Gustafsson message dated "Thu, 25 Jun 2020 16:18:59 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2270139.1593095554.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 25 Jun 2020 10:32:34 -0400 Message-ID: <2270140.1593095554@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Daniel Gustafsson writes: > PostgreSQL doesn't know which datatype you expect 'blub' to be, as it is= n't > related to the testtable relation in your query. If you cast to the dat= atype > of your choice you will get the expected result. > postgres=3D# SELECT COUNT(DISTINCT(testtable.column2, 'blub')) FROM publ= ic.testtable; > ERROR: could not identify a comparison function for type unknown The reason for this might be a little more obvious if you wrote the implicit row constructor explicitly, ie SELECT COUNT(DISTINCT ROW(testtable.column2, 'blub')) FROM public.testtabl= e; The row's datatype is indeterminate as-specified. Perhaps there's room to argue that we should allow 'unknown' to decay to 'text' automatically in this context, but I'm not in a big hurry to do that. It seems better to make people be explicit about which datatype they intend inside such complex, infrequently-used constructs. regards, tom lane