| 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: | Whole Thread | Raw Message | 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)
| 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 |