Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA96048 for ; Wed, 24 May 2000 19:07:49 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id TAA13011; Wed, 24 May 2000 19:07:48 -0400 (EDT) To: Joseph Shraibman cc: "pgsql-sql@postgresql.org" Subject: Re: possible bug with group by? In-reply-to: <392C5819.2FD488A8@selectacast.net> References: <392C5819.2FD488A8@selectacast.net> Comments: In-reply-to Joseph Shraibman message dated "Wed, 24 May 2000 18:30:49 -0400" Date: Wed, 24 May 2000 19:07:47 -0400 Message-ID: <13008.959209667@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/131 Joseph Shraibman writes: > playpen=> select a, b, case when c is null then 'not set' else 'set' end > as z from tablea group by a, b, z; > ERROR: Unable to identify an operator '<' for types 'unknown' and 'unknown' > You will have to retype this query using an explicit cast It's not GROUP BY's fault, it's just the oft-repeated issue about quoted string literals not having any definite type in Postgres. The parser postpones assigning a type until it sees the literal used in a context where a type can be determined --- and in a case like this, it never can. You need to force the issue with a cast, eg select a, b, case when c is null then 'not set'::text else 'set'::text end as z from tablea group by a, b, z; regards, tom lane