Thread: bug with distinct?

I dont' know if this a bug or I am just out of my mind, but since it
works with other RDBMS's I decided to ask, so here it goes:

I am trying to do the following:

     SELECT distinct 'mod_type' ,currval('mytable_idmytable_seq') from
mytable;

and I get the message:

ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
 Use an explicit ordering operator or modify the query

Thinking it could be because of the currval I tried the following:

     SELECT distinct 'mod_type', idmytable from mytable

getting the same result.

Am I doing something wrong? Is there a way to do this? Is this a bug?

Thanx

P.S. If someone is wondering what the heck I am trying to do.... I
pretend to insert into a log table, the mod_type for 'mytable' (in this
case insert) and the new inserted row for 'mytable'.

Cheers

Alfonso Peniche writes:

>      SELECT distinct 'mod_type' ,currval('mytable_idmytable_seq') from
> mytable;
>
> and I get the message:
>
> ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
>  Use an explicit ordering operator or modify the query

Use 'mod_type'::text.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Alfonso Peniche <alfonso@iteso.mx> writes:
>      SELECT distinct 'mod_type' ,currval('mytable_idmytable_seq') from
> mytable;
> ERROR:  Unable to identify an ordering operator '<' for type 'unknown'
>  Use an explicit ordering operator or modify the query

You need to give the literal an explicit type, eg,

     SELECT distinct 'mod_type'::text, currval('mytable_idmytable_seq') from
     mytable;

7.1 will default to assuming that you meant 'text' in this scenario,
but older releases are pickier.

            regards, tom lane

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

> Alfonso Peniche <alfonso@iteso.mx> writes:
> >      SELECT distinct \'mod_type\'
,currval(\'mytable_idmytable_seq\') from
> > mytable;
> > ERROR:  Unable to identify an ordering operator \'<\'
for type \'unknown\'
> >  Use an explicit ordering operator or modify the
query
>
> You need to give the literal an explicit type, eg,
>
>      SELECT distinct \'mod_type\'::text,
currval(\'mytable_idmytable_seq\')
> from
>      mytable;
>
> 7.1 will default to assuming that you meant \'text\' in
this scenario,
> but older releases are pickier.
>
>             regards, tom lane
>
Thanx, it woeks great now.

Cheers

---