X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BEBE852801 for ; Mon, 11 Jul 2005 12:32:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77452-05 for ; Mon, 11 Jul 2005 15:32:19 +0000 (GMT) Received: from resmta04.ono.com (smtp.ono.com [62.42.230.12]) by svr1.postgresql.org (Postfix) with ESMTP id 0302A52872 for ; Mon, 11 Jul 2005 12:32:17 -0300 (ADT) Received: from Zeus (84.120.16.82) by resmta04.ono.com (7.2.052) id 424AAD63008B1585 for pgsql-sql@postgresql.org; Mon, 11 Jul 2005 17:32:16 +0200 From: "Mauricio Fernandez" To: Subject: PostgreSQL - ERROR: Cannot cast type character varying to integer Date: Mon, 11 Jul 2005 17:32:36 +0200 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0014_01C5863E.873DDD40" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-MS-TNEF-Correlator: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.75 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST X-Spam-Level: * X-Archive-Number: 200507/97 X-Sequence-Number: 22075 This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C5863E.873DDD40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit 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 ------=_NextPart_000_0014_01C5863E.873DDD40 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IiQPAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEIAAUABAAAAAAAAAAAAAEJAAQAAgAAAAAA AAABBoADAA4AAADVBwcACwARAB8AAAABAB8BAQOQBgCICwAAKAAAAAsAAgABAAAACwAjAAAAAAAD ACYAAAAAAAsAKQAAAAAAAwAuAAAAAAADADYAAAAAAB4ATQABAAAAAQAAAAAAAAAeAHAAAQAAAEMA AABQb3N0Z3JlU1FMIC0gRVJST1I6ICBDYW5ub3QgY2FzdCB0eXBlIGNoYXJhY3RlciB2YXJ5aW5n IHRvIGludGVnZXIAAAIBcQABAAAAIAAAAAHFhhOulO3CbOJInElfpmd5u5zoRWsAAQJJsAAFCtIg CwAXDAAAAAACAR0MAQAAABkAAABTTVRQOk1GQUNPTlRBQ1RPQE9OTy5DT00AAAAACwABDgAAAABA AAYOAFIwii2GxQECAQoOAQAAABgAAAAAAAAAyW8h3k7AxEqHbRvDUNlxHcKAAAALAB8OAQAAAAIB CRABAAAA4AYAANwGAABdDwAATFpGdY0H4f8DAAoAcmNwZzEyNQYyAPgLYG5nMzA4HjIB9wKkA+MC AHBycREOUGZjaArAc2V0fDAgBxMCgwBQEGYRTn0lCoF2CJB3awuAZDRdDGBjAFALBANgdAWQdB8L pwqxCoIVlALRMSBIBGVsCQAgSVwnYjA0bSBuB9ELgCB0EGhlIGwEAHQsIGUAcGQX8CB3AHAFQHTZ F+BsZQrAA6BhGRAVoMsZcAbgdQVAcG8ZQAnByHNxbBlgbm8H4BgFNncFsBSRZxiiGsBkYd0BkWER gBl1EVB2GQAY4I8EABtwA2ACYGVtOhV6PxaFFp8elRrAAZEaYCBj/wdAGmAZoBV5HqAZMQWwDeD+ bx7QAjAeECNnF1MlAQIQxRfBdwQgKGp1GUAY05UZQHIU8HQIcGUpIOx3IFckLiDkKCkFIvAEcGmI Z29wCYBpZG8MhFkBoCB2CsARQigB0CnLCuMrGGgbkHBpAZADIXssLy05ZgWQEVAuxy8bMXY1LUgV gWQngSwPLRt1/wMAHcABABCwMV8tOyRRMP+/NFcrMCDkLUYg7xeAQRmb3wUAGnAVsBqxFFIgHgIZ oO8CIBjRHdAilXcfECbyHyF7C4AnVGkCIB/POp8VO3PmYjbwAUBzYUMyDwI28AAzNCBDUkVBVBBF IE9SB/BFUEyEQUNEsFZJRVc0EOR3XxWwbXAkPxkgGHBtJqBQK8MZYEYwshlgSNsuVRlgUDMlGWBV NXUZYG8IUCRRLUFHU0EF8EdEU2BFTEVDVCtFR7QgYRlgKHN1YidRHTEo+zCVA1IgF4ACEAXAREAt QMB8fCAnLSc5oUdT109RTV9OYzVOwzJPKE/fvU3cN1HXLUBLUTCjLEdEvytUSKZNETMWTRFTYG1N 4Ok1Zjo6C4B0TxJLUTVm71gVN7NZAA7wbx3QWVU3tWFHU0ZST00kL0dEV6xIRURwJqAoV4c9T3CA NzU5OTI4Jy1Ac0TRXs8zNl/0R0Ve2jQ5Q0AyMw9gX/9i9DU4FjBjgErWR1ywVVAg/EJZTEsZYFW0 Vm1XaUdE3UTQRF5wZhIwozs6rxeAXkIbURnAB0AZ4HkEIGd/EZAe9AeBQ3BsgEAhKB0g015wXLBS OiswQwBwHED3BUAjACbieSuwIvARUQDQdxWwBcA0IXkdMhoxWSFlTWyAcmqPF4BNeR2wZf8e0AkA K7AFwBzyGUAd0D/xPxigBCA1YASBGdAYsXhwoxlzG4ggOC4RsGIbUW8YsTMVdIIRgHIe0AXAKJRl eHChbi6hd2U0AK8uQT/gHUEKsXQYcHItQGUiF0wLgHV4B/AjQUjvPkED8BjgI2trBJEXsFIQAC40 LjIxLTI3q3bAfZAuS/BzRiAgc0AiUBuUU1FMVLAuM/YuFtpyBEkDoAkAIwFtAH8A0B8QR3EjEW+T HTEEIGL/EZB5IAnwHbNv4wQgdAMmIP9HYnb1eBU+8SvwB5ALkBgh7nR/oXIiBaBueEE/4gQg/xkg fPBYI1iMOYGD6RmCPMG/K3BxMhjxdgl/cYWRYwQgnxnAIwB38h7lb/NvZitBv4bYCsAZAAnwIrIZ oGJzQP8nE2xQRgFyBG8xJ0ADcHkw9wRwc0AY8Gx1oAeAe9SMpLsroA3gP22fcg8XgFQRUP5uhBEa w5LfQa8VpA8GKQXTk4U0AE1hCHFjP/BIIdMEoBgQZTF1AXoRwIZF/4DQbIADAASQF+ABAAYAGTH/ H6AqRknheEEAkDWREcAbUPkYEGYzHEAAwJvimXADAM56B0A1wZOTbWYA0AIhyXCBb0ACIG8uBaCP tYsEYBRQbG8ANjA3YWDpX6A2OJh2YpYfQk9DX7BQLkQuOaIW+VOQcf8jbyR/FyYipANgJoGMIoKw /22GDDCniStRR7Q4kytRSKb/OJMwozhXMxo1Wzekoqek0P4wpMEOIF+wLsNOoLGhZPD/LsgB0LLQ scGLkDiTYSQuyPsXgLMYM1+QLVWxr7K/s85/X9C077X/twMPQBGwt5c3v7g/tA+6X7tjtooPQDRE QM23lzi/Wb4jNjHB5b8P272JfdA1fZC2bDMOIL10/bfTNlIgws1jQMPfvdQuyLQ0Nb5QLsZPt3k2 t3S/yH/Jj8qfy67HILeIMc1/n86KugrA4MS3vIA3NsYv/9H/0w/OvzPi2WQ4k6IQDjD+LrZd11HI FL0UtmHYf9mP+9BzLsg5NvC6wdas3tW3Ev/DkN3P3t/Pr9C/1zPB6dd1/+Nf5G/lf+aP3bTn9FTA 6H+/6Y/aj9uf1zPEhLfTMNhP3+7P1PbEmdYf1yQ38o/zn+/0r8U/9t/HWTLgSflP338X4IuX/BQR AALACwABgAggBgAAAAAAwAAAAAAAAEYAAAAAA4UAAAAAAAADAAOACCAGAAAAAADAAAAAAAAARgAA AAAQhQAAAAAAAAMALIAIIAYAAAAAAMAAAAAAAABGAAAAAFKFAACOagEACwA5gAggBgAAAAAAwAAA AAAAAEYAAAAADoUAAAAAAAADADuACCAGAAAAAADAAAAAAAAARgAAAAARhQAAAAAAAAMAPIAIIAYA AAAAAMAAAAAAAABGAAAAABiFAAAAAAAAHgBvgAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAE AAAAOS4wAAsAcIAIIAYAAAAAAMAAAAAAAABGAAAAAAaFAAAAAAAAAwBxgAggBgAAAAAAwAAAAAAA AEYAAAAAAYUAAAAAAAAeAImACCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgCK gAggBgAAAAAAwAAAAAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4Ai4AIIAYAAAAAAMAAAAAAAABG AAAAADiFAAABAAAAAQAAAAAAAAALAJOACCAGAAAAAADAAAAAAAAARgAAAACChQAAAQAAAAIB+A8B AAAAEAAAAMlvId5OwMRKh20bw1DZcR0CAfoPAQAAABAAAADJbyHeTsDESodtG8NQ2XEdAgH7DwEA AACCAAAAAAAAADihuxAF5RAaobsIACsqVsIAAFBTVFBSWC5ETEwAAAAAAAAAAE5JVEH5v7gBAKoA N9luAAAAQzpcRG9jdW1lbnRzIGFuZCBTZXR0aW5nc1xNYXVyaWNpb1xNaXMgZG9jdW1lbnRvc1xN aSBDb3JyZW9cbWF1cmljaW8ucHN0AAAAAwD+DwUAAAADAA00/TcAAAIBfwABAAAAMwAAADxNRUVL SUhHTk5DS01IQkFIRFBOTkNFRUhDREFBLm1mYWNvbnRhY3RvQG9uby5jb20+AAADAAYQEzrpSAMA BxCvBwAAAwAQEAAAAAADABEQAAAAAB4ACBABAAAAZQAAAEhFTExPSbRNTkVXSU5USEVMSVNULEFO RElXQU5UVE9MRUFSTkFMT1RBQk9VVFBPU1RHUkVTUUwsTk9XSbRNV09SS0lOR0lOQURBVEFCQVNF QU5ESUhBVkVUSElTUFJPQkxFTToAAAAAtXM= ------=_NextPart_000_0014_01C5863E.873DDD40--