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

[Mauricio Fernandez A.]

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


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

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

[Jaime Casanova]

On 1/11/06, Mauricio Fernandez A. <[EMAIL PROTECTED]> 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 ;)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

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

[Tom Lane]

"Mauricio Fernandez A." <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] psql client: technique for applying default values to :variables?

[Jeff Boes]

Stumped: is there any way to set up default values for psql variables
within the .SQL file itself? Obviously, I can do something like:

$ psql -f my_script -v MYVAR=${myvar:-mydefault}

but I would prefer to have the value stored with the .SQL file, e.g. (if
this actually worked):

\set MYVAR COALESCE(:MYVAR,'mydefault')

-- 
Jeffery Boes  <><
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] 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,   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:[EMAIL PROTECTED]
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." <[EMAIL PROTECTED]> 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


---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] foreign keys with on delete cascade and triggers

[Dirk Jagdmann]

Hello,

I often create foreign keys with "on delete cascade" so I can
conviniently delete rows in multiple tables which are referenced by (a
chain) of foreign keys. Now I've run into an issue and I'd like to
have some opinions if the current behaviour of PostgreSQL is desired.
If have made my tests with versions 8.0.4 and 8.1.1.

The idea behind the sample commands below is, that the whole deletion
should be denied, because a trigger in a cascaded table blocked the
deletion. The trigger works as expected and prevents rows with a value
of "5" being deleted from table "b". However if the deletion was
triggered via the cascaded foreign key (trigger), the deletion in
table "a" is not rolled back, thus the row with "5" in "a" is lost.
This of course leaves the database in an inconsistant state, because
the foreign key in table "b" can no longer be referenced in "a".

Now I'd like to know if this is a bug in the current form of cascaded
deletions; or if this is desired behaviour and the oppression of
deletions via triggers is undefined behaviour in the cascaded case; or
if this issue just hasn't been addressed yet; or something completly
differnt.

create table a ( i int primary key );
create table b ( f int references a on delete cascade on update cascade );
create or replace function f() returns trigger as $$
BEGIN
  IF OLD.f = 5 THEN
RETURN NULL;
  END IF;
  RETURN OLD;
END;
$$ language plpgsql;
create trigger b_del before delete on b for each row execute procedure f();
insert into a values(5);
insert into b values(5);
delete from a where i=5;
select * from a; -- 0 rows
select * from b; -- 1 row containing '5'

--
---> Dirk Jagdmann
> http://cubic.org/~doj
-> http://llg.cubic.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] psql client: technique for applying default values to :variables?

[Tony Wasson]

On 1/11/06, Jeff Boes <[EMAIL PROTECTED]> wrote:
> Stumped: is there any way to set up default values for psql variables
> within the .SQL file itself? Obviously, I can do something like:
>
> $ psql -f my_script -v MYVAR=${myvar:-mydefault}
>
> but I would prefer to have the value stored with the .SQL file, e.g. (if
> this actually worked):
>
> \set MYVAR COALESCE(:MYVAR,'mydefault')

Stuff like this works for me in a SQL file

\set edate 'CURRENT_DATE::DATE'

SELECT * FROM some_table WHERE update_date = :edate;

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] foreign keys with on delete cascade and triggers

[Tom Lane]

Dirk Jagdmann <[EMAIL PROTECTED]> writes:
> The idea behind the sample commands below is, that the whole deletion
> should be denied, because a trigger in a cascaded table blocked the
> deletion. The trigger works as expected and prevents rows with a value
> of "5" being deleted from table "b". However if the deletion was
> triggered via the cascaded foreign key (trigger), the deletion in
> table "a" is not rolled back, thus the row with "5" in "a" is lost.
> This of course leaves the database in an inconsistant state, because
> the foreign key in table "b" can no longer be referenced in "a".

> Now I'd like to know if this is a bug in the current form of cascaded
> deletions; or if this is desired behaviour and the oppression of
> deletions via triggers is undefined behaviour in the cascaded case; or
> if this issue just hasn't been addressed yet; or something completly
> differnt.

This is a bug in your trigger design.  The database is doing what it's
supposed to do, ie, trying to delete the dependent row.  If you turn
that into a no-op, it's your own fault if you don't like the resulting
state.

If you want the whole transaction rolled back, raise an error instead
of returning NULL.

(This has been discussed before ... see the archives.)

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly