pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnable to identify an ordering operator '<' for type 'smallint[]'
4+ messages / 3 participants
[nested] [flat]
* Unable to identify an ordering operator '<' for type 'smallint[]'
@ 2006-01-11 17:55 Mauricio Fernandez A. <mfacontacto@ono.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mauricio Fernandez A. @ 2006-01-11 17:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable to identify an ordering operator '<' for type 'smallint[]'
@ 2006-01-11 18:14 Jaime Casanova <systemguards@gmail.com>
parent: Mauricio Fernandez A. <mfacontacto@ono.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jaime Casanova @ 2006-01-11 18:14 UTC (permalink / raw)
To: Mauricio Fernandez A. <mfacontacto@ono.com>; +Cc: pgsql-sql
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 ;)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable to identify an ordering operator '<' for type 'smallint[]'
@ 2006-01-11 18:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mauricio Fernandez A. <mfacontacto@ono.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-01-11 18:24 UTC (permalink / raw)
To: Mauricio Fernandez A. <mfacontacto@ono.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable to identify an ordering operator '<' for type 'smallint[]'
@ 2006-01-11 20:07 Mauricio Fernandez A. <mfacontacto@ono.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Mauricio Fernandez A. @ 2006-01-11 20:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-01-11 20:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-11 17:55 Unable to identify an ordering operator '<' for type 'smallint[]' Mauricio Fernandez A. <mfacontacto@ono.com>
2006-01-11 18:14 ` Jaime Casanova <systemguards@gmail.com>
2006-01-11 18:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-11 20:07 ` Mauricio Fernandez A. <mfacontacto@ono.com>
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