[SQL] PostgreSQL - ERROR: Cannot cast type character varying to integer
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 > ( > codigopedidovarchar(20) > codigohospital varchar(20) > fecha varchar(15) > productovarchar(20) > unidadesvarchar(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 > [EMAIL PROTECTED] > movil: 607365968 > > P.D. Some historicoventas table rows can be: > codPedido codHospital fecha productounidades > costo > 0001001299100058 20050103759936 1 > 2375 > 0001001299100058 20050103759928 1 > 2375 > 0001001300100078 20050103759936 1 > 2375 > 0001003044100086 20050614759936 1 > 2275.25 > 0001003128100062 20050623759936 2 > 4550.5 > 0001003129100069 20050623759936 2 > 4550.5 > 0001003131100019 20050623759928 5 > 11376.25 > 0001003131100019 20050623759936 3 > 6825.75 > 0001003132100075 20050623759936 4 > 9101 > 0001003133100106 20050623759936 2 > 4550.5 > 0001003134100031 20050623759936 2 > 4550.5 > 0001003135100037 20050623759936 3 > 6825.75 > 000100313619 20050623759936 5 > 11376.25 > 000100313719 20050623759936 1 > 2275.25 > 0001003138100024 20050623759936 4 > 9101 <> ---(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] PostgreSQL - ERROR: Cannot cast type character varying to integer
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Convert numeric to money
inspect your cast functions with: 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; Un saludo. Mauricio Fernandez A. Ingeniero de Sistemas Universidad Autonoma de Manizales [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Bruno Wolff III Enviado el: viernes, 22 de julio de 2005 19:30 Para: [EMAIL PROTECTED] CC: pgsql-sql@postgresql.org Asunto: Re: [SQL] Convert numeric to money On Fri, Jul 22, 2005 at 11:03:40 -0300, [EMAIL PROTECTED] wrote: > Hi. > I have searched in mailing-list archives about converting types, but I couldn't > found a function or clause that convert a numeric type to money type. > How Can I convert this types? > > => select '1234'::money; >money > > R$1.234,00 > > => select '1234'::numeric::money; > ERROR: cannot cast type numeric to money > > The problem is becouse I have a table with "numeric" field, and I need to show > it like "money" type (R$ 1.234,00). Is there a function to convert it??? Or is > there a function to mask the numeric field to show like money (with > R$x.xxx,xx)??? You probably want to use to_char to convert the numeric value to a string which can be displayed. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Character set error
Good Day. I´m working with postgres 7.4. on Win XP and when I do a query like select* from table it returns me this error message: Invalid character data was found. This is most likely caused by stored data containing characters that are invalid for the character set the database was created in. The most common example of this is storing 8bit data in a SQL_ASCII database. I know it´s a character set problem, I really have SQL_ASCII encoding but I don´t know how to fix it Any idea will be welcome Thanks Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Como ejecutar una funcion insert en plpgsql....
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Fernando lo que pasa es que postgres siempre pasa a minusculas los nombres de los campos o tablas excepto si estos se escriben entre comillas. es decir algo como: select CAMPO1 ... postrgres lo pasara a: select campo1 .. y algo como select "CamPo1" ... postgres lo mantendra igual. Sobre la ejecucion de la instruccion: la tienes en una funcion? o solo estas probando que te inserta algun dato de prueba, si es asi no tienes que usar el execute. Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Fernando Garcia Enviado el: miércoles, 09 noviembre, 2005 15:22 Para: pgsql-sql@postgresql.org Asunto: [SQL] Como ejecutar una funcion insert en plpgsql Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago yo trabajo con el editor postgresql manager pro.. Gracias... Ahh otra cosa: en otra consulta que realice pero que es un select al principio me daba un error porque no encontraba la columna "ItemID" la cual porsupuesto existia, entonces a la columna le quite las mayusculas de su nombre quedando "itemid" y ya no me salio el error.en postgres los nombres de tablas y columnas deben ser siempre con minuscula -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQA/AwUBQ3IUEc4irmZP1c6WEQKdagCg86HUkABRxiUkwoX0KxatXgBrVIMAnRl6 Fejv6bN92riROQl+LG+pA6zN =+KQq -END PGP SIGNATURE-
[SQL] does indexes need statistics?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello everybody, Is it necesary the statistics be enabled when I create indexes or to use them? thanks Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQA/AwUBQ4X7u84irmZP1c6WEQLdzgCfS0S7kfz6DOdv1pPd9w9iBvKpYaYAn3bL efk9bmafKvgTFjUxWZV1jGR/ =I7D6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Anual Query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, Can any of you help me with this query?, I need to retrieve the sum(units) and sum(cost) for each client in every month in the same row, something like this: client|UnJan|CostJan|UnFeb|CostFeb|UnMar|CostMar ...|UnDec|CostDec - 100058|580 |47384 |746 |87438 |536 |21653 ... |857|754875 (wrong data, just to show) Now I get this result using several querys (one for month and worse for client too) but this, of course its too expensive. Here is a table sample. orderId | client | date| product | Un | cost - -- 1001299 | 100058 | 20050103 | 759936 | 1 | 2375 1001299 | 100058 | 20050103 | 759928 | 1 | 2375 1001300 | 100078 | 20050103 | 759936 | 1 | 2375 1001301 | 100017 | 20050103 | 759928 | 1 | 2375 1001302 | 19 | 20050103 | 759936 | 2 | 4750 1001302 | 19 | 20050203 | 756304 | 1 | 6669 1001303 | 19 | 20050203 | 759936 | 2 | 4750 1001304 | 15 | 20050203 | 759936 | 20 | 47500 1001306 | 100013 | 20050203 | 759936 | 2 | 4750 1001306 | 100013 | 20050203 | 759928 | 2 | 4750 1001307 | 100013 | 20050203 | 759936 | 4 | 9500 1001308 | 100013 | 20050203 | 759936 | 2 | 4750 1001309 | 100050 | 20050303 | 759936 | 2 | 4750 1001310 | 100050 | 20050303 | 759936 | 4 | 9500 Thanks for any idea. Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQA/AwUBQ4sgNc4irmZP1c6WEQLcPgCeLQNXphTXFXqVFZcKxrIO7aAfCEMAn397 jOO4lULVn/3bPLm6du7hGJeR =YSsL -END PGP SIGNATURE- ---(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] 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 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[]'
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
Re: [SQL] Slow update SQL
You can try this too:...WHERE
EXTRACT(YEAR FROM dxdate::Date) > 2000
Mauricio Fernández A.Ingeniero de
SistemasUniversidad Autónoma de Manizales (Colombia)
-Mensaje
original-De: [EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]En
nombre de Bungsuputra LinanEnviado el: lunes, 27 febrero, 2006 3:42Para:
[EMAIL PROTECTED]; Postgres HelpAsunto: Re: [SQL] Slow update
SQLHi Ken,I used to have the same problem. In my computer,
using date_part in WHEREclause will always slow down the system when the
table has plenty of rows.My suggestion is try to change the query
to:... WHERE dxdate >= '2001-01-01';I hope this
helps.Regards,bungsu- Original Message -From:
Ken HillTo: Postgres HelpSent: Tuesday, February 14, 2006 8:48
AMSubject: [SQL] Slow update SQLI'm experiencing a very slow
query. The table contains 611,564 rows of data.I vaccumed the
table:VACUUM ANALYZE ncccr10;SELECT count(*) FROM
ncccr10;count611564(1 row)When I try to analyze
the query plan with:EXPLAIN ANALYZEUPDATE ncccr10SET key =
facilityno||'-'||lastname||'-'||sex||'-'||ssno||'-'||birthdate||'-'||primarysit||'-'||dxdate||'-'||morphology3WHERE
date_part('year',dxdate) > '2000';The query just never finishes (even
1 hour later). The colum key100 isindexed, and I'm setting the value of
thiscolumn from other columns. Why is this so
slow?---(end of
broadcast)---TIP 6: explain analyze is your
friend
Re: [SQL] how to add primary key to existing table with multiple primary keys
Your table has a primary key yet, so you can´t add a second primary key. You only can define one pk, however it can be composed as your table has (it now has 2 columns), so I think you need to drop the current pk and add again the new pk with your 3 columns (probeid, tissueid, expid). Review this http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2038 Mauricio Fernández A.Ingeniero de SistemasUniversidad Autónoma de Manizales (Colombia) -Mensaje original-De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]En nombre de Daniel JooEnviado el: viernes, 24 febrero, 2006 2:45Para: pgsql-sql@postgresql.orgAsunto: [SQL] how to add primary key to existing table with multiple primary keys Hi there, I am trying to add another primary key to an existing table with two other primary keys. I got the following error when I tried this command: alter table extprobe2tissue ADD primary key (expid); ERROR: multiple primary keys for table "extprobe2tissue" are not allowed The details of the extprobe2tissue table is: \d extprobe2tissue; Table "public.extprobe2tissue" Column | Type | Modifiers -++--- probeid | integer | not null tissueid | integer | not null value | double precision | not null entrydate | date | not null description | character varying(200) | qval | double precision | expid | integer | not null Indexes: "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid) "extprobe2tissue_probeid" btree (probeid) "extprobe2tissue_tissueid" btree (tissueid) Foreign-key constraints: "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES tissue(tissueid) "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES extprobe(probeid) Any suggestion would be much appreciated. Thanks!
