pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError with DISTINCT and AS keywords
5+ messages / 5 participants
[nested] [flat]
* Error with DISTINCT and AS keywords
@ 2002-07-10 11:57 Andreas Schlegel <schlegel@software.b.uunet.de>
2002-07-10 12:25 ` Re: Error with DISTINCT and AS keywords Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-10 15:22 ` Re: Error with DISTINCT and AS keywords Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Andreas Schlegel @ 2002-07-10 11:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error with DISTINCT and AS keywords
2002-07-10 11:57 Error with DISTINCT and AS keywords Andreas Schlegel <schlegel@software.b.uunet.de>
@ 2002-07-10 12:25 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-10 13:31 ` Re: Error with DISTINCT and AS keywords Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Achilleus Mantzios @ 2002-07-10 12:25 UTC (permalink / raw)
To: Andreas Schlegel <schlegel@software.b.uunet.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error with DISTINCT and AS keywords
2002-07-10 11:57 Error with DISTINCT and AS keywords Andreas Schlegel <schlegel@software.b.uunet.de>
2002-07-10 12:25 ` Re: Error with DISTINCT and AS keywords Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2002-07-10 13:31 ` Richard Huxton <dev@archonet.com>
2002-07-10 14:09 ` Re: Error with DISTINCT and AS keywords Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2002-07-10 13:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error with DISTINCT and AS keywords
2002-07-10 11:57 Error with DISTINCT and AS keywords Andreas Schlegel <schlegel@software.b.uunet.de>
2002-07-10 12:25 ` Re: Error with DISTINCT and AS keywords Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-10 13:31 ` Re: Error with DISTINCT and AS keywords Richard Huxton <dev@archonet.com>
@ 2002-07-10 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-07-10 14:09 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Error with DISTINCT and AS keywords
2002-07-10 11:57 Error with DISTINCT and AS keywords Andreas Schlegel <schlegel@software.b.uunet.de>
@ 2002-07-10 15:22 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-07-10 15:22 UTC (permalink / raw)
To: Andreas Schlegel <schlegel@software.b.uunet.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-07-10 15:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-10 11:57 Error with DISTINCT and AS keywords Andreas Schlegel <schlegel@software.b.uunet.de>
2002-07-10 12:25 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-10 13:31 ` Richard Huxton <dev@archonet.com>
2002-07-10 14:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-10 15:22 ` Jean-Luc Lachance <jllachan@nsd.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox