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

[Torsten Förtsch <tfoertsch123@xxxxxxxxx>]

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@xxxxxxxxxx> 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