Обсуждение: [GENERAL] change type from NUMERIC(14,4) to NUMERIC(24,12)
Hi,
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.
Thanks,
Torsten
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
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
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:
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
=?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