Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA88113 for ; Wed, 24 May 2000 19:03:25 -0400 (EDT) (envelope-from reedstrm@rice.edu) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Wed, 24 May 2000 18:03:25 -0500 (CDT) Date: Wed, 24 May 2000 18:03:25 -0500 From: "Ross J. Reedstrom" To: "pgsql-sql@postgresql.org" Subject: Re: possible bug with group by? Message-ID: <20000524180325.A19078@rice.edu> Mail-Followup-To: "pgsql-sql@postgresql.org" References: <392C5819.2FD488A8@selectacast.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <392C5819.2FD488A8@selectacast.net>; from jks@selectacast.net on Wed, May 24, 2000 at 06:30:49PM -0400 X-Archive-Number: 200005/127 On Wed, May 24, 2000 at 06:30:49PM -0400, Joseph Shraibman wrote: > Is this a bug or am I just misunderstanding something? > Not a bug, pgsql is just less willing to cast things willy-nilly in 7.0 than it was in 6.x. In this case, the system doesn't know what 'not set' and 'set' are supposed to be, so if can't decide what operator to use to compare them for grouping. Try something like: 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; Or even SQL92 compliant: select a, b, case when c is null then CAST ('not set' AS TEXT) else CAST ('set' as text) end as z from tablea group by a, b, z; > > 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 > playpen=> > Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005