Thread: Don't understand error?
Hi everyone, I'm working with a large spatial dataset at the moment. About 109m rows. What I do is join some other tables to it, do some maths and spatial joins, and then put the results into a new table (which is therefore 109m rows too). When I run my SQL for 50 people it's fine. 1 person is fine. A thousand people it's fine. But when I try and run it for the whole dataset I'm given this error: ERROR: value out of range: underflow ****************Error ******************* Error: Value out of range: underflow SQL state: 22003 I've looked this up, but I don't really get what it is saying. I'd appreciate some advice? I was going to post my actual SQL code but it's about 150 lines long and I wasn't sure it would be useful. Thanks James
James David Smith <james.david.smith@gmail.com> writes: > When I run my SQL for 50 people it's fine. 1 person is fine. A > thousand people it's fine. But when I try and run it for the whole > dataset I'm given this error: > ERROR: value out of range: underflow > ****************Error ******************* > Error: Value out of range: underflow > SQL state: 22003 This looks like an arithmetic problem not a SQL problem. Is your calculation working with very small floating-point values anywhere? regards, tom lane
Hi Tom, Yes, I thought the same. Glad to know that I might be along the right lines. I've started taking parameters out one-by-one to try and find the culprit. Yes, there are a variety of numbers stored as NUMERIC in various places. For example: 0.23423424234 5.6434535 10.2131 etc. Then there are various multiplications and divisions occurring in fairly complex formulas. I guess I just slowly work through the code and try to figure out which bit of the maths is causing the error? Cheers James On 14 April 2014 14:58, Tom Lane <tgl@sss.pgh.pa.us> wrote: > James David Smith <james.david.smith@gmail.com> writes: >> When I run my SQL for 50 people it's fine. 1 person is fine. A >> thousand people it's fine. But when I try and run it for the whole >> dataset I'm given this error: > >> ERROR: value out of range: underflow >> ****************Error ******************* >> Error: Value out of range: underflow >> SQL state: 22003 > > This looks like an arithmetic problem not a SQL problem. Is your > calculation working with very small floating-point values anywhere? > > regards, tom lane
James David Smith <james.david.smith@gmail.com> writes: > Yes, I thought the same. Glad to know that I might be along the right > lines. I've started taking parameters out one-by-one to try and find > the culprit. Yes, there are a variety of numbers stored as NUMERIC in > various places. For example: AFAIR, there is no such thing as an underflow error in the type-NUMERIC code --- it will happily round to zero instead. You should be looking for something involving float4 or float8 (aka real/double precision). regards, tom lane
Good tip. Thanks. I don't think there are many values stored like that, so it should make it a bit easier to find. On 14 April 2014 15:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > James David Smith <james.david.smith@gmail.com> writes: >> Yes, I thought the same. Glad to know that I might be along the right >> lines. I've started taking parameters out one-by-one to try and find >> the culprit. Yes, there are a variety of numbers stored as NUMERIC in >> various places. For example: > > AFAIR, there is no such thing as an underflow error in the type-NUMERIC > code --- it will happily round to zero instead. You should be looking for > something involving float4 or float8 (aka real/double precision). > > regards, tom lane
Just to say thanks. It looks like the error originated from multiplying the result of an EXTRACT(EPOCH from time) . Once I added ::numeric to this, the query ran ok. I also changed a couple of integer types to numeric, so it might have been those either.
On 14 Apr 2014 15:25, "James David Smith" <james.david.smith@gmail.com> wrote:
Good tip. Thanks. I don't think there are many values stored like
that, so it should make it a bit easier to find.
On 14 April 2014 15:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> James David Smith <james.david.smith@gmail.com> writes:
>> Yes, I thought the same. Glad to know that I might be along the right
>> lines. I've started taking parameters out one-by-one to try and find
>> the culprit. Yes, there are a variety of numbers stored as NUMERIC in
>> various places. For example:
>
> AFAIR, there is no such thing as an underflow error in the type-NUMERIC
> code --- it will happily round to zero instead. You should be looking for
> something involving float4 or float8 (aka real/double precision).
>
> regards, tom lane