CTAS. Difference in scale / precision for decimal and numeric columns between original and target tables

[Pavel Zotov]

Consider script:

create table tbase(
    id int128
   ,dec_02_2 decimal(2,2)
   ,dec_04_0 decimal(4)
   ,dec_04_2 decimal(4,2)
   ,dec_10_0 decimal(10,0)
   ,dec_10_10 decimal(10,10)
    ------------------------
   ,num_02_2 numeric(2,2)
   ,num_04_0 numeric(4)
   ,num_04_2 numeric(4,2)
   ,num_10_0 numeric(10)
   ,num_10_10 numeric(10,10)
);
commit;
create table ctas_permanent as (select * from tbase);
commit;
show table tbase;
show table ctas_permanent;

Its output will be:

show table tbase;
Table: PUBLIC.TBASE
ID                              INT128 Nullable
DEC_02_2                        DECIMAL(2, 2) Nullable
DEC_04_0                        DECIMAL(4, 0) Nullable
DEC_04_2                        DECIMAL(4, 2) Nullable
DEC_10_0                        DECIMAL(10, 0) Nullable
DEC_10_10                       DECIMAL(10, 10) Nullable
NUM_02_2                        NUMERIC(2, 2) Nullable
NUM_04_0                        NUMERIC(4, 0) Nullable
NUM_04_2                        NUMERIC(4, 2) Nullable
NUM_10_0                        NUMERIC(10, 0) Nullable
NUM_10_10                       NUMERIC(10, 10) Nullable

show table ctas_permanent;
Table: PUBLIC.CTAS_PERMANENT
ID                              INT128 Nullable
DEC_02_2                        DECIMAL(9, 2) Nullable
DEC_04_0                        DECIMAL(9, 0) Nullable
DEC_04_2                        DECIMAL(9, 2) Nullable
DEC_10_0                        DECIMAL(18, 0) Nullable
DEC_10_10                       DECIMAL(18, 10) Nullable
NUM_02_2                        NUMERIC(4, 2) Nullable
NUM_04_0                        NUMERIC(4, 0) Nullable
NUM_04_2                        NUMERIC(4, 2) Nullable
NUM_10_0                        NUMERIC(18, 0) Nullable
NUM_10_10                       NUMERIC(18, 10) Nullable 

Is it expected that DECIMAL (and some NUMERIC) scale/precision differ in origin table vs CTAS ?

 

[Denis Simonov]

This isn't an error. The scale is preserved, and the precision is set to the maximum for the internal storage type. 

The reason is that precision information isn't stored in the input/output message metadata. 

Furthermore, the query expressions are usually more complex than simply retrieving a column from another table.

If this were a CREATE TABLE <table-name> LIKE <other-table> statement, the complaint would be justified.

четверг, 2 июля 2026 г. в 20:46:20 UTC+3, Pavel Zotov:

[Adriano dos Santos Fernandes]

Fixed. Now:

SQL> show table ctas_permanent;

Table: PUBLIC.CTAS_PERMANENT
ID INT128 Nullable
DEC_02_2 DECIMAL(2, 2) Nullable
DEC_04_0 DECIMAL(4, 0) Nullable
DEC_04_2 DECIMAL(4, 2) Nullable
DEC_10_0 DECIMAL(10, 0) Nullable
DEC_10_10 DECIMAL(10, 10) Nullable
NUM_02_2 NUMERIC(2, 2) Nullable
NUM_04_0 NUMERIC(4, 0) Nullable
NUM_04_2 NUMERIC(4, 2) Nullable
NUM_10_0 NUMERIC(10, 0) Nullable
NUM_10_10 NUMERIC(10, 10) Nullable

Adriano