X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E2C084761A3 for ; Tue, 4 Feb 2003 09:53:53 -0500 (EST) Received: from jester.inquent.com (unknown [216.208.117.7]) by postgresql.org (Postfix) with ESMTP id 677E1476446 for ; Tue, 4 Feb 2003 09:28:47 -0500 (EST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by jester.inquent.com (8.12.6/8.12.6) with ESMTP id h14ETDtm080319; Tue, 4 Feb 2003 09:29:13 -0500 (EST) (envelope-from rbt@rbt.ca) Subject: Re: SELECT DISTINCT is picky about constants From: Rod Taylor To: Andreas Pflug Cc: Postgresql Performance In-Reply-To: <3E3FAEF2.5080302@web.de> References: <3E3FAEF2.5080302@web.de> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-yo6G1kLGsKXTV7F96m7t" Organization: Message-Id: <1044368952.80167.6.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 04 Feb 2003 09:29:13 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/11 X-Sequence-Number: 1067 --=-yo6G1kLGsKXTV7F96m7t Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Tue, 2003-02-04 at 07:15, Andreas Pflug wrote: > The query > SELECT DISTINCT keycol, 'constant' FROM myTable > or > SELECT DISTINCT keycol, NULL FROM myTable >=20 > will result in an error message (7.3.1) >=20 > Unable to identify an ordering operator '<' for type "unknown" > Use explicit ordering operator or modify query >=20 > If I use 'constant'::varchar or NULL::varchar everything's fine.=20 > Unfortunately, this SELECT DISTINCT will appear quite often in my app. >=20 > I'd rather like PostgreSQL to use implicit type casting for such=20 > constants. The final type chosen doesn't matter anyway and life would be= =20 > easier. How about: SELECT keycol, NULL FROM (SELECT DISTINCT keycol FROM myTable) AS tab; Might even be quicker as you won't have to do any comparisons against the constant. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-yo6G1kLGsKXTV7F96m7t Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) iD8DBQA+P8446DETLow6vwwRAlZEAJ4l08Uoo8XeIL5Ub1FDwmgWiu+hNwCdEO1h ejcMssrNS7jLWaaNSFfowVk= =p9BA -----END PGP SIGNATURE----- --=-yo6G1kLGsKXTV7F96m7t--