Обсуждение: 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
Re: Unable to identify an ordering operator '<' for type 'smallint[]'
От
"Mauricio Fernandez A."
Дата:
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