From: felix.quintgz@yahoo.com
To: pgsql-general@lists.postgresql.org
Subject: Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
Date: Fri, 1 Mar 2024 13:15:24 +0000 (UTC)
Message-ID: <358718951.3118444.1709298924143@mail.yahoo.com> (raw)
In-Reply-To: <44cwjpheabf3fadoc2fhrr73py3cgdirv3khf3hdco62ew2yej@rzvta7dgu7gt>
References: <CAJyMCYK0jzjRt6P0rqhahvYen_yCMPJwwSSsf9v9YGa3ju3MnA@mail.gmail.com>
<ukrcpvts37hb2oew56d6mtax4p6xwpnugvgzedv4vr7c6vclry@76gy76wybcd5>
<CAJyMCYK-ZCOxHkjheUUmfrFyWzNW586kj2bnT_hgDSe0XEPr=g@mail.gmail.com>
<CAJyMCYKovV7FQ=Ck0r76wrvjDCsO=J4D3Gxx-OLm457OyVTddA@mail.gmail.com>
<44cwjpheabf3fadoc2fhrr73py3cgdirv3khf3hdco62ew2yej@rzvta7dgu7gt>
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 <ewie@ewie.name> 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? Maybe type numeric(19,18) is intentional.
> On Thu, 29 Feb 2024 at 08:11, Anthony Apollis <anthony.apollis@gmail.com>
> 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? In plain SQL you can
certainly insert numeric into text columns. Postgres does an implicit
cast in that case. Not sure what your middleware does in that case
though.
> > On Thu, 29 Feb 2024 at 02:12, Erik Wienhold <ewie@ewie.name> 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 seem
> >> 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=1, ColumnName=Metric
> >> > Value [DataType=DT_NUMERIC,Length=0], Data=[Length=12,
> >> > Value=311116655.63]
> >> > at
> >> ZappySys.PowerPack.Adapter.SqlDataComponentBase.HandleException(Exception
> >> > ex)
> >> > at ZappySys.PowerPack.Adapter.SqlDataComponentBase.PrimeOutput(Int32
> >> > outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
> >> > 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=19
> >> and scale=18 which would be numeric(19,18). But value 311116655.63
> >> shown in the error message requires a precision that is at least 9
> >> digits larger than the scale:
> >>
> >> regress=# select '311116655.63'::numeric(19,18);
> >> ERROR: numeric field overflow
> >> DETAIL: A field with precision 19, scale 18 must round to an
> >> absolute value less than 10^1.
> >> regress=# select '311116655.63'::numeric(27,18);
> >> numeric
> >> ------------------------------
> >> 311116655.630000000000000000
> >> (1 row)
--
Erik
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: felix.quintgz@yahoo.com
Subject: Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
In-Reply-To: <358718951.3118444.1709298924143@mail.yahoo.com>
References: <CAJyMCYK0jzjRt6P0rqhahvYen_yCMPJwwSSsf9v9YGa3ju3MnA@mail.gmail.com> <ukrcpvts37hb2oew56d6mtax4p6xwpnugvgzedv4vr7c6vclry@76gy76wybcd5> <CAJyMCYK-ZCOxHkjheUUmfrFyWzNW586kj2bnT_hgDSe0XEPr=g@mail.gmail.com> <CAJyMCYKovV7FQ=Ck0r76wrvjDCsO=J4D3Gxx-OLm457OyVTddA@mail.gmail.com> <44cwjpheabf3fadoc2fhrr73py3cgdirv3khf3hdco62ew2yej@rzvta7dgu7gt> <358718951.3118444.1709298924143@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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