Обсуждение: Unable to identify an ordering operator

Hello,

select persons.name,firm.name,persons.tel      from persons,work,firm       where (persons.table_owner=0) and
 (work.id_firm=firm.id and work.id_person=persons.id)          union  select persons.name,firm.name,persons.tel
from persons,work,firm          where (persons.table_owner=1) and                (work.id_firm=firm.id and
work.id_person=persons.id);

ERROR:  Unable to identify an ordering operator '<' for type '_text'       Use an explicit ordering operator or modify
thequery
 


I know that this query can be rewrite to eliminate the UNION using an
OR operator on first condition but my question is:

How can I specify an ordering operator? On which field have I to put it?


Best wishes for the new YEAR,              \fer

Ferruccio Zamuner <nonsolosoft@diff.org> writes:
> select persons.name,firm.name,persons.tel
>        from persons,work,firm 
>        where (persons.table_owner=0) and 
>              (work.id_firm=firm.id and work.id_person=persons.id) 
>           union
>    select persons.name,firm.name,persons.tel 
>           from persons,work,firm 
>           where (persons.table_owner=1) and 
>                 (work.id_firm=firm.id and work.id_person=persons.id);

> ERROR:  Unable to identify an ordering operator '<' for type '_text'

UNION is defined to eliminate duplicate rows, so it has to be able to
sort and compare rows, which means it needs < and = operators for each
selected column's datatype.  Evidently one of the name or tel columns
is declared as array of text ('_text' means text[]), for which there
is no '<' operator.  You could probably make one if you were intent
on it (see contrib/array for some inspiration).  A lazier way out is
to use UNION ALL, which doesn't try to eliminate duplicates.
        regards, tom lane