Thread: Error with DISTINCT and AS keywords
Hi, I need some help to let this sql statement run with Postgres 7.2.1 Doesn't work: select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; ERROR: Unable to identify an ordering operator '<' for type 'unknown' Use an explicit ordering operator or modifythe query If I remove the DISTINCT keyword it works: select tnr, titel, 'TEST' AS testcol from tTitel; Greetings, Andreas
On Wed, 10 Jul 2002, Andreas Schlegel wrote: > Hi, > > I need some help to let this sql statement run with Postgres 7.2.1 > > Doesn't work: > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query Do it as select DISTINCT tnr, titel, 'TEST'::text AS testcol from tTitel; > > If I remove the DISTINCT keyword it works: > select tnr, titel, 'TEST' AS testcol from tTitel; > > > > Greetings, > Andreas > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
On Wednesday 10 Jul 2002 1:25 pm, Achilleus Mantzios wrote: > On Wed, 10 Jul 2002, Andreas Schlegel wrote: > > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > > Use an explicit ordering operator or modify the query > > Do it as > > select DISTINCT tnr, titel, 'TEST'::text AS testcol from tTitel; Does anyone know what type a quoted literal has? The error message reports "unknown" - is that because it may be a date? It does strike me as a little unexpected that a quoted string doesn't default to text. - Richard Huxton
Richard Huxton <dev@archonet.com> writes: > Does anyone know what type a quoted literal has? It's assigned the placeholder type UNKNOWN until the parser can figure out from context what type it should be. Unfortunately in this case there's no context the parser knows how to work with. > It does strike me as a little > unexpected that a quoted string doesn't default to text. Yeah, I have a TODO item to let unknown be resolved to text when the only available context is an ORDER BY (or equivalently DISTINCT) request. I've looked at this a little bit but not figured out where the cleanest place to do it is. regards, tom lane
PostgreSQL does not know how to sort 'TEST'. You must help it be telling it what tpe it is. Add ::text after 'TEST' as in 'TEST'::text. Maybe PostgreSQL should default to text for unknown types... JLL Andreas Schlegel wrote: > > Hi, > > I need some help to let this sql statement run with Postgres 7.2.1 > > Doesn't work: > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > > If I remove the DISTINCT keyword it works: > select tnr, titel, 'TEST' AS testcol from tTitel; > > Greetings, > Andreas > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org