Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rg2jh-003rFq-9Z for pgsql-general@arkaria.postgresql.org; Fri, 01 Mar 2024 13:15:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rg2je-00DLdM-Or for pgsql-general@arkaria.postgresql.org; Fri, 01 Mar 2024 13:15:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rg2je-00DLdC-3i for pgsql-general@lists.postgresql.org; Fri, 01 Mar 2024 13:15:34 +0000 Received: from sonic312-23.consmr.mail.ne1.yahoo.com ([66.163.191.204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rg2jZ-002G0B-PV for pgsql-general@lists.postgresql.org; Fri, 01 Mar 2024 13:15:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1709298926; bh=J/sjvYPUtstG89NdFSeeVs0mw1yW0Ry/xa24yEbW4FU=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=Qy8Etcd0dtp93VeNMmD7T/lJ/QurUbsTI/OCyyYyAi37WSSNlf7mTUZubb7D2V/gsn6f3SFz5Xnyq7AXJvgQhLgq8dnbAVlnoOhlefg3Lk5pT2lU2MKBPiy93XN6c91FxwRNBgo7UzFkxKyW2PdZ/t5MPBP4yev+HlAqy4OCHd66VFz3mvWuvezGmLoCwzkw2jfEdrLbyP6kAbV03hmGfCNzgRl1D7gIWRVFBLoVr7gAbaUTDhVPTZODalvoqRIhBBEBXPb+dzcKBnO7DJKFJ9v5aAM7kzk0x+W8bMWxJ2O982MSP2p91rfpPRWyWNMt4Xq6gX92wuGosEORH5B8Xg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1709298926; bh=PmYeunKpFIYh3/qUWkvjxt39XMZv9Wl+bM5jMHX6NoK=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=KydTXFUVTEWsgJDOskiO76+WmMttfGUkDomCCLlfpofOy5JjGJaN7zKw7LAmQbh0TYjW+wOx6bpQp19cc8TpFY5UOwvEx4w1cUgrh8bGptKJAo99CCHunlRzUV+hZQnT7C+FXc6VTl09cFsF3Im1NIFTix1EPJ+cpfgYRsu2M5eULrVdZrzA9rdmwoH70R3b9/JoHBQxbAZ2EUgeQLn2Z6HlxL51Vebwh+wdh/P3vzftl/Vi+atERWv0NbV0AWilERr+gvc5/sVEzfjGUxtiRN3nkqU6gTjPDCpx4iuF2zEVVSzMoZnUhzVrgKWUnEW3xVZnHRSlp5Lf6I0v3wb3pg== X-YMail-OSG: XCMg4IUVM1nyingiPh_K0cZH1EQFnbHoZw8lrkX6DiY5DG0VH8YqSmXJ4CoDBnk 2UXyYbprmE3NS7LDpW9eISuM1.kLuNaWgd92ygTwKhFRy970xdfUsiXOZXVY143CJAiTtjm1LruR QolVN04Nf34j628w8PQ1h6NjsiGZIoYReBRy0P8MYvef2Z_jtJ7mRhGPXvp8qstAx72IQ6xSxBeL r.nk_EWdjz.HRlfxycOY1HKmryDRd8rUjJAR.xh2gtJ8y3ttre9K5nHaT6kuCb9Q4DyTIjfnP0e3 QrybZj6la8XXarNdhc_GXOchhq0r3lyJHcIz30dQZd72qD38dNDP3PpK5WqDBJEORY4uVn0P9hd7 uRVQkmA6NQbBH59za4qNy.3eMLAaUxzNuJHiNMvJQRjYCOa16sKjaSJbFxu6di7WALknnmRGNVbR Vc0GTyfbFVgxgbPXHf6SXQ7aKx5a968wY1lbfBHBVAdvr8TnSVaqdHkjcAlF23SNUy3gJcN6rBK_ rvqUqrmVdgHzDEgzsCOMm.gej2KFcfy4sZtb0YrvxpE9eAsdhQhHXi8Cf0njCEAypR.EtmBVqjbg Jxb0NV7SqW4iD.FTBNVQtXPXPUUQjWP_0CnjbCZ4sfUkE1vU.cr259IQxXftF.Gv1NvfAkfGGrne dGGtNpjmEdXG8hGRBLTxyRHazn_W_0vxjjT.i09ZFd3zJg0l3yOSqI6q4FRGzaUVWF1yry5Ebhdj xVF9M07hhKu0CwF0gWQoUOnPo7RMttG_8hSXzNAJ0U4zytnWZUsakpwP7IJWCKxczeqVv_2R_IM7 Y0UvD3.ylMNatJSEdIAfi7DHe1qqndkhTxUrf46cDGMQ4XMi9h49toZCJRCfJ8vCEArUJEZdMVK. dmBuJG.xNSoPZwzrVglscGrM3eeuOor7oqPx1jgcuhgTOId1ddEeWIzN0LJ1RRMHupFYUGeQyujr mXfn0cUqM4o0O3gbh9i.L9AvRoWqtAd6Km5KK21VGQGxO0NO2UvrL6sehOOPDi05TitQFv4EAs63 5Og3mGS0vpV4rl7UTiUf6Gqv5fM1cMPU1cxgdCOe1TO0vy1LMiR83aUf3ejS4PBpa.MeM5IUMIOJ 1HxLad2cLMXQhgcigAjvHojQVlZt3XAMkrfDxj1rHdACKig_86pcrKOJfOv0nuKVJaJJ5qfC8cVo o95mK9q2FNiGVf4nM3MBgJchnhsR9WGV3XNYH4bdE2DIf7adzZJaRo7RrSY9YSPNa0V3MMCWHaNU R7CuNs5Xmmg41F2EPlw178fI5J1Xsnf6ht_o3wmGCvGj2JmWYVoZ9lXUsKyiX7uU6sWvFx9d9DjV QHE_UMEdkB9ho5rKdu2ZSMPNZRNrkIYOUY09t4PgtlDdniuJDiA2bceUbBkzzUTQcxh66GsVDdHt zt0Ph9wHYbDRT7LKOVzqGq2dKV1iwQQ2VQBIxq.P_SLJcKUTTUbQJoJC_ihBWPNCjIrHv5v50AN4 qB_RtchIg88pLki.SlDPwyuRMWLQ2MBj7qreEX_GmqLBJfGSo1SAIX7Oa6uiJTzgK.yO2W5PA2Lo byA.22kA2vx.uRMkfF7DAmhlNhiD7JIJnVr6i.gv6rymq23jIftPreucBckycuyPNN5JmM6RH2De Q_JO9NXiZ9AZU63R2l90G6_11ukGZ.g6tO0XPUqlfGNbaJ0.YpwXeNddJsLBRkYEyYGxHlpy3m1t uauk9B9o6ZMahAVVCO0Kq.RV9fWRuVNqUkpEJdH2gdWczia9KPixtXJgS8..DtEiRhubyxHCRQqe G4I3RgQHK65y8W0gmsqoCBc8zr5xNab.WIW1FAKDhEGrBVXcNsszsmf88XALvcgMJV06iU540FLS Fls.eD296rNdkzKUNYDUbBt52hyD0.XwN2xX0uIdix3q60aa9jQqNwIBh7ZhcjjH0SfCdqQ1KFA2 uRZoS8K4EKE1CW_7aPaaoKugeY7NkCON5FGZb7k4qcNCv_05JCZRWA6n_c76jf3PqvXosc0WIXGt Gj0rbTxpPtKw2CalbiwLgTokhS6jT9Fka_kpKCNofYtEb_PSjbCQl8AgE1ykFxM6qm4TkFSb8pOV KB8NPL6AVcJDfpN1GGDr6qKVUH7NAQ9ESNsEKN1nZYqTu.kWfNF4rsd7onowWNI3BVgct.Fncx2_ FIRgQQGutxfQ1cEdVfHdDEzqaFHA7ky.IyIOqcd2VGdlxpNqn.EKCZ6NHAAmnPVSoY6q_mTKsH5y GdF6Vvg-- X-Sonic-MF: X-Sonic-ID: c449f297-fa14-44e8-b238-8ae680bd2d9a Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.ne1.yahoo.com with HTTP; Fri, 1 Mar 2024 13:15:26 +0000 Date: Fri, 1 Mar 2024 13:15:24 +0000 (UTC) From: felix.quintgz@yahoo.com To: pgsql-general@lists.postgresql.org Message-ID: <358718951.3118444.1709298924143@mail.yahoo.com> In-Reply-To: <44cwjpheabf3fadoc2fhrr73py3cgdirv3khf3hdco62ew2yej@rzvta7dgu7gt> References: <44cwjpheabf3fadoc2fhrr73py3cgdirv3khf3hdco62ew2yej@rzvta7dgu7gt> Subject: Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Mailer: WebService/1.1.22103 YMailNodin Content-Length: 3258 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The numeric(19, 18) field type can only store numbers less than 10 with 18 = decimal places. To save the number 311116655.63 you need a numeric(12, 2) field type. On Thursday, February 29, 2024 at 10:00:10 AM GMT-5, Erik Wienhold wrote: cc'ing list. On 2024-02-29 07:16 +0100, Anthony Apollis wrote: > I cant change data type using the advance editor in destination: > [image: image.png] > [image: image.png] The column type must be changed in the database itself. On second thought, are you sure "Metric Value" is the right destination column for your import?=C2=A0 Maybe type numeric(19,18) is intentional. > On Thu, 29 Feb 2024 at 08:11, Anthony Apollis > wrote: > > > I tried multiple data types, including: "Metric Value" text COLLATE > > pg_catalog."default", "Metric Value" character varying(510) COLLATE > > pg_catalog."default", and Decimal/Numeric. Did you get the same error with the text columns?=C2=A0 In plain SQL you ca= n certainly insert numeric into text columns.=C2=A0 Postgres does an implicit cast in that case.=C2=A0 Not sure what your middleware does in that case though. > > On Thu, 29 Feb 2024 at 02:12, Erik Wienhold wrote: > > > >> On 2024-02-28 21:22 +0100, Anthony Apollis wrote: > >> > Please assist. I am using SSIS to read data from an Excel sheet into > >> > Postgres. I have increased the column size a few times, just cant se= em > >> to > >> > get the data in. Getting errors relating to destination column size. > >> > >> What is the data type of that column? > >> > >> > [Excel Source [24]] Error: System.Exception: SqlTruncateException: > >> > Numeric arithmetic causes truncation.. RowNumber=3D1, ColumnName=3DM= etric > >> > Value [DataType=3DDT_NUMERIC,Length=3D0], Data=3D[Length=3D12, > >> > Value=3D311116655.63] > >> >=C2=A0 =C2=A0 at > >> ZappySys.PowerPack.Adapter.SqlDataComponentBase.HandleException(Except= ion > >> > ex) > >> >=C2=A0 =C2=A0 at ZappySys.PowerPack.Adapter.SqlDataComponentBase.Prim= eOutput(Int32 > >> > outputs, Int32[] outputIDs, PipelineBuffer[] buffers) > >> >=C2=A0 =C2=A0 at > >> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(= IDTSManagedComponentWrapper100 > >> > wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, > >> > IntPtr ppBufferWirePacket) > >> > >> Not sure what I'm looking at, but screenshots 1 and 2 say precision=3D= 19 > >> and scale=3D18 which would be numeric(19,18).=C2=A0 But value 31111665= 5.63 > >> shown in the error message requires a precision that is at least 9 > >> digits larger than the scale: > >> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 regress=3D# select '311116655.63'::numeric(= 19,18); > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ERROR:=C2=A0 numeric field overflow > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 DETAIL:=C2=A0 A field with precision 19, sc= ale 18 must round to an > >> absolute value less than 10^1. > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 regress=3D# select '311116655.63'::numeric(= 27,18); > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 n= umeric > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ------------------------------ > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 311116655.630000000000000000 > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (1 row) -- Erik