Обсуждение: 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