change type from NUMERIC(14,4) to NUMERIC(24,12)

Torsten Förtsch wrote:

we have a large table and want to change the type of one column from NUMERIC(14,4) to NUMERIC(24,12).
If the new type is just NUMERIC without any boundaries, the operation is fast. If (24,12) is
specified, it takes ages.

I think it takes so long because the database wants to check that all data in the table is compatible
with the new type. But the old type has stricter boundaries both before and after the dot. So, it is
compatible. It has to be.

Is there a way to change the type skipping the additional check?

This is 9.6.

If you don't mind doing something unsupported, you could just modify
the attribute metadata in the catalog:

test=# CREATE TABLE a(x numeric(14,4));
CREATE TABLE
test=# INSERT INTO a VALUES (1234567890.1234);
INSERT 0 1
test=# UPDATE pg_attribute
SET atttypmod = atttypmod + (24 - 14) * 65536 + (12 - 4)
WHERE attrelid = 'a'::regclass AND attname = 'x';
UPDATE 1
test=# \d a
Table "public.a"
Column | Type | Modifiers
--------+----------------+-----------
x | numeric(24,12) |

test=# SELECT * FROM a;
x
-----------------
1234567890.1234
(1 row)

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I found that myself. But ...

postgres=# create table x(n14_4 NUMERIC(14,4), n24_12 NUMERIC(24,12), n
NUMERIC);
CREATE TABLE
postgres=# insert into x select i+.4, i+.12, i+.5234543 from
generate_series(1,1000000) i;
INSERT 0 1000000
postgres=# select * from x order by n limit 5;
n14_4 | n24_12 | n
--------+----------------+-----------
1.4000 | 1.120000000000 | 1.5234543
2.4000 | 2.120000000000 | 2.5234543
3.4000 | 3.120000000000 | 3.5234543
4.4000 | 4.120000000000 | 4.5234543
5.4000 | 5.120000000000 | 5.5234543
(5 rows)

postgres=# \d x
Table "tf.x"
Column | Type | Modifiers
--------+----------------+-----------
n14_4 | numeric(14,4) |
n24_12 | numeric(24,12) |
n | numeric |

postgres=# begin; alter table x alter column n14_4 type NUMERIC(24,12);
select * from x order by n limit 5; abort;
BEGIN
ALTER TABLE
n14_4 | n24_12 | n
----------------+----------------+-----------
1.400000000000 | 1.120000000000 | 1.5234543
2.400000000000 | 2.120000000000 | 2.5234543
3.400000000000 | 3.120000000000 | 3.5234543
4.400000000000 | 4.120000000000 | 4.5234543
5.400000000000 | 5.120000000000 | 5.5234543
(5 rows)

ROLLBACK
postgres=# \d x
Table "tf.x"
Column | Type | Modifiers
--------+----------------+-----------
n14_4 | numeric(14,4) |
n24_12 | numeric(24,12) |
n | numeric |

postgres=# select * from x order by n limit 5;
n14_4 | n24_12 | n
--------+----------------+-----------
1.4000 | 1.120000000000 | 1.5234543
2.4000 | 2.120000000000 | 2.5234543
3.4000 | 3.120000000000 | 3.5234543
4.4000 | 4.120000000000 | 4.5234543
5.4000 | 5.120000000000 | 5.5234543
(5 rows)

postgres=# begin; update pg_attribute set atttypmod=1572880 where
attrelid='x'::regclass::oid and attname='n14_4'; select * from x order by n
limit 5;
BEGIN
UPDATE 1
n14_4 | n24_12 | n
--------+----------------+-----------
1.4000 | 1.120000000000 | 1.5234543
2.4000 | 2.120000000000 | 2.5234543
3.4000 | 3.120000000000 | 3.5234543
4.4000 | 4.120000000000 | 4.5234543
5.4000 | 5.120000000000 | 5.5234543
(5 rows)

postgres=# \d x
Table "tf.x"
Column | Type | Modifiers
--------+----------------+-----------
n14_4 | numeric(24,12) |
n24_12 | numeric(24,12) |
n | numeric |

postgres=# abort;
ROLLBACK

As you can see, after the ALTER TABLE command the n14_4 column is shown
with 12 places after the dot. If I just update atttypmod, it's still only 4
places.

Why is that so? I checked ctid. The ALTER TABLE version does not actually
update the tuple.

On Tue, Jan 24, 2017 at 11:48 AM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:

=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:

we have a large table and want to change the type of one column
from NUMERIC(14,4) to NUMERIC(24,12). If the new type is just NUMERIC
without any boundaries, the operation is fast. If (24,12) is specified, it
takes ages.

I think it takes so long because the database wants to check that all data
in the table is compatible with the new type. But the old type has stricter
boundaries both before and after the dot. So, it is compatible. It has to
be.

Nope, numeric_transform doesn't think that:

* Flatten calls to numeric's length coercion function that solely represent
* increases in allowable precision. Scale changes mutate every datum, so
* they are unoptimizable.

The point being that "12.0000" is not quite the same thing as
"12.000000000000" --- they may represent notionally equal values, but
they're stored with different dscale fields, and that in turn has
consequences for subsequent arithmetic operations.

If you're willing to live with different dscales in the column, I'd
suggest just going to unconstrained numeric.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general