Thread: Unable to identify an ordering operator '<' for type 'smallint[]'

Hello I have been working in a report based on the query bellow.  It works
fine in my development server which is postgres 8.0 but in production I have
postgres 7.3 and I am getting the following error:

SQL Error :
ERROR:  Unable to identify an ordering operator '<' for type 'smallint[]'       Use an explicit ordering operator or
modifythe query
 

In declaration:
select ac.idanalisis, ac.idpt, pt.codigohospital, ct.nombre as
nombrehospital,        pt.codigomedico, m.nombre || ' ' || m.apellidos as nombremedico,
ac.pacact,       ac.potencialpacientes, ac.planvisitas
from plannegocio.analisiscliente ac       inner join plannegocio.planterritorio pt on ac.idpt = pt.idpt       inner
joincentrotrabajo ct on pt.codigohospital = ct.codigo       inner join medico m on pt.codigomedico = m.codigo
 
where ac.idpt in       (select pt.idpt from plannegocio.planterritorio pt where pt.idplan =
1)
group by ac.idanalisis, ac.idpt, pt.codigohospital, ct.nombre,
pt.codigomedico,       nombremedico, ac.pacact, ac.potencialpacientes, ac.planvisitas
order by ct.nombre, nombremedico

The only strange thing in the query is that ac.potencialpacientes is
smallint[] (in fact is the only thing with sense I can see in the error
message) but I am not using the '<' operator as it suggest.

Can somebody help me?.
thanks in advance

Mauricio Fernández A.
Ingeniero de Sistemas
U. Autónoma de Manizales

On 1/11/06, Mauricio Fernandez A. <mfacontacto@ono.com> wrote:
> Hello I have been working in a report based on the query bellow.  It works
> fine in my development server which is postgres 8.0 but in production I have
> postgres 7.3 and I am getting the following error:
>
> SQL Error :
> ERROR:  Unable to identify an ordering operator '<' for type 'smallint[]'
>        Use an explicit ordering operator or modify the query
>
> In declaration:
> select ac.idanalisis, ac.idpt, pt.codigohospital, ct.nombre as
> nombrehospital,
>         pt.codigomedico, m.nombre || ' ' || m.apellidos as nombremedico,
> ac.pacact,
>        ac.potencialpacientes, ac.planvisitas
> from plannegocio.analisiscliente ac
>        inner join plannegocio.planterritorio pt on ac.idpt = pt.idpt
>        inner join centrotrabajo ct on pt.codigohospital = ct.codigo
>        inner join medico m on pt.codigomedico = m.codigo
> where ac.idpt in
>        (select pt.idpt from plannegocio.planterritorio pt where pt.idplan =
> 1)
> group by ac.idanalisis, ac.idpt, pt.codigohospital, ct.nombre,
> pt.codigomedico,
>        nombremedico, ac.pacact, ac.potencialpacientes, ac.planvisitas
> order by ct.nombre, nombremedico
>
> The only strange thing in the query is that ac.potencialpacientes is
> smallint[] (in fact is the only thing with sense I can see in the error
> message) but I am not using the '<' operator as it suggest.
>
> Can somebody help me?.
> thanks in advance
>
> Mauricio Fernández A.
> Ingeniero de Sistemas
> U. Autónoma de Manizales
>
>

that's why is a bad idea to develop in version that is superior to the
one you will use in production...

my advice, upgrade your production server to 8.1.2... there were some
work in arrays in 8.x.x

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

"Mauricio Fernandez A." <mfacontacto@ono.com> writes:
> The only strange thing in the query is that ac.potencialpacientes is
> smallint[] (in fact is the only thing with sense I can see in the error
> message) but I am not using the '<' operator as it suggest.

No, but you're trying to GROUP BY ac.potencialpacientes, and in 7.3 the
only way to do grouping is sort/unique.  So you have to be able to sort
the datatype, and 7.3 doesn't have code to do comparison of arrays.

> Can somebody help me?.

Upgrade.  7.3 is pretty ancient anyway.  Quite aside from lack of features,
its performance is poor compared to 8.1.
        regards, tom lane

Thanks Jaime, I know, you are right : it´s a very bad idea to develop in
version that is superior to the
one in production, but, mmmm....  I don`t know, you win ;).

And thanks to Tom too, because he told the key phrase "GROUP BY
ac.potencialpacientes", as I can skip that clause, now I get the result
expected

Thanks for your help

Mauricio Fernández A.
Ingeniero de Sistemas
U. Autónoma de Manizales

-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviado el: miércoles, 11 enero, 2006 19:25
Para: Mauricio Fernandez A.
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Unable to identify an ordering operator '<' for type
'smallint[]'


"Mauricio Fernandez A." <mfacontacto@ono.com> writes:
> The only strange thing in the query is that ac.potencialpacientes is
> smallint[] (in fact is the only thing with sense I can see in the error
> message) but I am not using the '<' operator as it suggest.

No, but you're trying to GROUP BY ac.potencialpacientes, and in 7.3 the
only way to do grouping is sort/unique.  So you have to be able to sort
the datatype, and 7.3 doesn't have code to do comparison of arrays.

> Can somebody help me?.

Upgrade.  7.3 is pretty ancient anyway.  Quite aside from lack of features,
its performance is poor compared to 8.1.
        regards, tom lane