pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL - ERROR: Cannot cast type character varying to integer
3+ messages / 2 participants
[nested] [flat]
* PostgreSQL - ERROR: Cannot cast type character varying to integer
@ 2005-07-11 15:32 Mauricio Fernandez <mfacontacto@ono.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mauricio Fernandez @ 2005-07-11 15:32 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
winmail.dat (application/ms-tnef, 3.0K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL - ERROR: Cannot cast type character varying to
@ 2005-07-12 11:34 Christoph Haller <ch@rodos.fzk.de>
parent: Mauricio Fernandez <mfacontacto@ono.com>
0 siblings, 1 reply; 3+ messages in thread
From: Christoph Haller @ 2005-07-12 11:34 UTC (permalink / raw)
To: Mauricio Fernandez <mfacontacto@ono.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL - ERROR: Cannot cast type character varying to integer
@ 2005-07-12 12:49 Mauricio Fernandez <mfacontacto@ono.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Mauricio Fernandez @ 2005-07-12 12:49 UTC (permalink / raw)
To: pgsql-sql
thanks for the answer. i get it too as
sum(int4(unidades::text))
Maybe somebody will need this answer too, so i want to add some info I get
from other list:
We can know wich CAST we have in our DB with this view:
create view showcasts as
select t.typname as source, t1.typname as target, p.proname as function,
(select case when c.castcontext = 'e'
then 'Must use Explicit Cast'
else ( select case when c.castcontext = 'i'
then 'Implicit cast for expressions and assignments'
else 'Implicit cast only for assignments'
end)
end ) as casttype
from pg_cast c, pg_type t, pg_type t1, pg_proc p
where c.castsource = t.oid and
c.casttarget = t1.oid and
c.castfunc = p.oid;
I hope this can be helpfull.
again thanks to all.
-----Mensaje original-----
De: ch@rodos.fzk.de [mailto:ch@rodos.fzk.de]
Enviado el: martes, 12 de julio de 2005 13:35
Para: Mauricio Fernandez
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] PostgreSQL - ERROR: Cannot cast type character varying to
integer
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-12 12:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-11 15:32 PostgreSQL - ERROR: Cannot cast type character varying to integer Mauricio Fernandez <mfacontacto@ono.com>
2005-07-12 11:34 ` Re: PostgreSQL - ERROR: Cannot cast type character varying to Christoph Haller <ch@rodos.fzk.de>
2005-07-12 12:49 ` Re: PostgreSQL - ERROR: Cannot cast type character varying to integer Mauricio Fernandez <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