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

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(dot)albe(at)wien(dot)gv(dot)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
>