Thread: PostgreSQL - ERROR: Cannot cast type character varying to integer

PostgreSQL - ERROR: Cannot cast type character varying to integer

From
"Mauricio Fernandez"
Date:
Hello I´m new in the list, and I want to learn a lot about postgresql, now
I´m working in a database and I have this problem:

I have a table called historicoventas as follows (just the structure)

>  historicoventas
> (
>   codigopedido        varchar(20)
>   codigohospital         varchar(20)
>   fecha             varchar(15)
>   producto        varchar(20)
>   unidades        varchar(10)
>   costo             varchar(20)
> )
>
And I want to create a view based on that table whit this instruction:

> CREATE OR REPLACE VIEW vw_temphistoricoventas
> (Pedido, Fecha, Hospital, Producto, Unidades, Costo)
> AS
> SELECT codigoPedido , (substring( fecha from 1 for 4 ) || '-'
> || substring( fecha from 5 for 2 ) || '-'
> || substring( fecha from 7 for 2 ) ) AS fecha,
> codigoHospital , producto , sum( unidades::int4 ) AS unidades, sum(
> costo::float4 ) AS costo
> FROM historicoventas
> WHERE ((producto = '759928') OR (producto = '759936') OR
> (producto = '4002382') OR (producto = '4002580'))
> GROUP BY codigoPedido, fecha, codigoHospital, producto
> ORDER BY fecha;
But I always get this message:
> ERROR:  Cannot cast type character varying to integer
My developer workstation is under win xp and postgresql 8.0 but in
production server (external web hosting partner) I have a Linux Red Hat with
kernel 2.4.21-27.0.2.ELsmp y PostgreSQL 7.3.
In local machine all castings between data types works fine but in server it
doesn´t.
convertions like sum(unidades::int4)  works fine and reading the postgresql
7.3 docs I can see this type of convertions are enabled by the system
Can somebody help me with this topic?

Thanks a lot

>
> Mauricio Fernández A.
> Ingeniero de Sistemas
> Universidad Autónoma de Manizales
> mfacontacto@ono.com
> movil: 607365968
>
> P.D.  Some historicoventas table rows can be:
> codPedido    codHospital    fecha        producto    unidades
> costo
> 0001001299     100058         20050103     759936         1
> 2375
> 0001001299     100058         20050103     759928         1
> 2375
> 0001001300     100078         20050103     759936         1
> 2375
> 0001003044     100086         20050614     759936         1
> 2275.25
> 0001003128     100062         20050623     759936         2
> 4550.5
> 0001003129     100069         20050623     759936         2
> 4550.5
> 0001003131     100019         20050623     759928         5
> 11376.25
> 0001003131     100019         20050623     759936         3
> 6825.75
> 0001003132     100075         20050623     759936         4
> 9101
> 0001003133     100106         20050623     759936         2
> 4550.5
> 0001003134     100031         20050623     759936         2
> 4550.5
> 0001003135     100037         20050623     759936         3
> 6825.75
> 0001003136     100009         20050623     759936         5
> 11376.25
> 0001003137    100009         20050623     759936         1
> 2275.25
> 0001003138     100024         20050623     759936         4
> 9101

Attachment

Re: PostgreSQL - ERROR: Cannot cast type character varying to

From
Christoph Haller
Date:
Under PostgreSQL 7.3 it was necessary 
to use an intermediate cast to text 
when converting from varchar to int4. 
sum( unidades::text::int4 ) should do. 

Regards, Christoph 


Mauricio Fernandez wrote:
> 
> Hello I´m new in the list, and I want to learn a lot about postgresql, now
> I´m working in a database and I have this problem:
> 
> I have a table called historicoventas as follows (just the structure)
> 
> >  historicoventas
> > (
> >   codigopedido                varchar(20)
> >   codigohospital              varchar(20)
> >   fecha                       varchar(15)
> >   producto            varchar(20)
> >   unidades            varchar(10)
> >   costo                       varchar(20)
> > )
> >
> And I want to create a view based on that table whit this instruction:
> 
> > CREATE OR REPLACE VIEW vw_temphistoricoventas
> > (Pedido, Fecha, Hospital, Producto, Unidades, Costo)
> > AS
> > SELECT codigoPedido , (substring( fecha from 1 for 4 ) || '-'
> > || substring( fecha from 5 for 2 ) || '-'
> > || substring( fecha from 7 for 2 ) ) AS fecha,
> > codigoHospital , producto , sum( unidades::int4 ) AS unidades, sum(
> > costo::float4 ) AS costo
> > FROM historicoventas
> > WHERE ((producto = '759928') OR (producto = '759936') OR
> > (producto = '4002382') OR (producto = '4002580'))
> > GROUP BY codigoPedido, fecha, codigoHospital, producto
> > ORDER BY fecha;
> But I always get this message:
> > ERROR:  Cannot cast type character varying to integer
> My developer workstation is under win xp and postgresql 8.0 but in
> production server (external web hosting partner) I have a Linux Red Hat with
> kernel 2.4.21-27.0.2.ELsmp y PostgreSQL 7.3.
> In local machine all castings between data types works fine but in server it
> doesn´t.
> convertions like sum(unidades::int4)  works fine and reading the postgresql
> 7.3 docs I can see this type of convertions are enabled by the system
> Can somebody help me with this topic?
> 
> Thanks a lot
> 
> >
> > Mauricio Fernández A.
> > Ingeniero de Sistemas
> > Universidad Autónoma de Manizales
> > mfacontacto@ono.com
> > movil: 607365968
> >
> > P.D.  Some historicoventas table rows can be:
> > codPedido     codHospital     fecha           producto        unidades
> > costo
> > 0001001299    100058          20050103        759936          1
> > 2375
> > 0001001299    100058          20050103        759928          1
> > 2375
> > 0001001300    100078          20050103        759936          1
> > 2375
> > 0001003044    100086          20050614        759936          1
> > 2275.25
> > 0001003128    100062          20050623        759936          2
> > 4550.5
> > 0001003129    100069          20050623        759936          2
> > 4550.5
> > 0001003131    100019          20050623        759928          5
> > 11376.25
> > 0001003131    100019          20050623        759936          3
> > 6825.75
> > 0001003132    100075          20050623        759936          4
> > 9101
> > 0001003133    100106          20050623        759936          2
> > 4550.5
> > 0001003134    100031          20050623        759936          2
> > 4550.5
> > 0001003135    100037          20050623        759936          3
> > 6825.75
> > 0001003136    100009          20050623        759936          5
> > 11376.25
> > 0001003137    100009          20050623        759936          1
> > 2275.25
> > 0001003138    100024          20050623        759936          4
> > 9101
> 
>   ------------------------------------------------------------------------
>                   Name: winmail.dat
>    winmail.dat    Type: application/ms-tnef
>               Encoding: base64
> 
>   ------------------------------------------------------------------------
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly