cant delete row

From: "Tamsin" <tg_mail(at)bryncadfan(dot)co(dot)uk>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: cant delete row
Date: 2001-04-05 09:13:56
Message-ID: NEBBKHBOBMJCHDMGKCNJGEBKCMAA.tg_mail@bryncadfan.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I have a row in a table which I cannot update/delete. I think it might be
something to do with the numeric(12,2) fields - I know during testing I
tried to insert values which were too big.
I can update other rows in the table, but if I try to update/delete this
one, I get the error
ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 8
scale 2

[guilty admission - I think maybe the numeric(12,2) fields used to be
numeric(8,2) fields and I increased them by messing with pg_attribute.
which could have something to do with it, although it seemed to work at the
time. that'll teach me]

I've tried dumping/reimporting the database, vacuum/analyzing the table etc.

I'm using Postgres 7.0.2 on linux. All the table details are below.
Thanks for any ideas!
Tamsin

test=# \d order_head

Table "order_head"
Attribute | Type | Modifier
---------------------+---------------+------------------------
order_head_id | integer | not null
order_value | numeric(12,2) | not null default 0
order_cost | numeric(12,2) | not null default 0
shipping_cost | numeric(8,2) |

test=# select order_cost, order_value, shipping_cost from order_head where
order_head_id = 1581;
order_cost | order_value | shipping_cost
---------------+---------------+---------------
5397499900.00 | 9714420700.00 |
(1 row)

test=# delete from order_head where order_head_id = 1581;
ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 8
scale 2
test=#

test=# select * from pg_attribute where attname in
('order_cost','order_value','shipping_cost') and attrelid in (select oid
from pg_class where relname = 'order_head');
attrelid | attname | atttypid | attdisbursion | attlen | attnum |
attnelems | attcacheoff | atttypmod | attbyval | attstorage | attisset |
attalign | attnotnull | atthasdef
----------+---------------+----------+---------------+--------+--------+----
-------+-------------+-----------+----------+------------+----------+-------
---+------------+-----------
1145957 | order_value | 1700 | 0.0283164 | -1 | 15 |
0 | -1 | 786438 | f | p | f | i |
t | t
1145957 | order_cost | 1700 | 0.0283164 | -1 | 20 |
0 | -1 | 786438 | f | p | f | i |
t | t
1145957 | shipping_cost | 1700 | 0.945491 | -1 | 23 |
0 | -1 | 524294 | f | p | f | i |
f | f
(3 rows)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matt Allen 2001-04-05 09:35:32 Re: Relation XX does not exist
Previous Message Igor Velkov 2001-04-05 09:13:33 copy from multi-line text problem