Exact numeric types–INTEGER, INT, BIGINT, TINYINT, INT8, SMALLINT, and all NUMERIC values of scale <=18 and precision 0 are interchangeable. For NUMERIC data types, you cannot alter precision, but you can change the scale in the ranges (0-18), (19-37), and so on.
Is the above statement still true in supported versions? 9.0,9.1, and 9.2? NUMERIC data types, you cannot alter precision, but you can change the scale in the ranges (0-18), (19-37), and so on.
Bryan, should this work is this the correct message or is something else possibly wrong? See below:
Customer is trying the following:
ALTER TABLE adw.dim_prod_bkup ALTER COLUMN otlt_svc_lvl SET DATA TYPE numeric(5,4);
But they are getting this error:
[Code: 2377, SQL State: 0A000] [Vertica]VJDBC ROLLBACK: Cannot convert column "otlt_svc_lvl" from "numeric(3,2)" to type "numeric(5,4)".
Thanks
No, can't change the scale (second digit). You can change the precision (first number) within the ranges allowed, e.g.:
dbadmin=> create table numbers (otlt_svc_lvl numeric(3,2));
CREATE TABLE
dbadmin=> ALTER TABLE numbers ALTER COLUMN otlt_svc_lvl SET DATA TYPE numeric(5,4);
ROLLBACK 2377: Cannot convert column "otlt_svc_lvl" from "numeric(3,2)" to type "numeric(5,4)"
dbadmin=> ALTER TABLE numbers ALTER COLUMN otlt_svc_lvl SET DATA TYPE numeric(5,2);
ALTER TABLE
It is, but not directly on the alter table/alter column page. You have to follow the link to the data type conversion page a few posts back. Ask the doc team to update the alter table/alter column page if you think we should make this clear there.
Comments
ALTER TABLE (https://my.vertica.com/docs/6.1.x/HTML/index.htm#1295.htm)
types–INTEGER, INT, BIGINT, TINYINT, INT8, SMALLINT, and all NUMERIC values of scale <=18 and precision 0 are interchangeable. For NUMERIC data types, you cannot alter precision, but you can change the scale in the ranges (0-18), (19-37), and so on.
Is the above statement still true in supported versions? 9.0,9.1, and 9.2? NUMERIC data types, you cannot alter precision, but you can change the scale in the ranges (0-18), (19-37), and so on.
This restriction still applies:
"INTEGER, INT, BIGINT, TINYINT, INT8, SMALLINT, and all NUMERIC values of scale <=18 and precision 0 are interchangeable.
For NUMERIC data types, you cannot alter scale, but you can change the precision in the ranges (0-18), (19-37), and so on."
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ColumnManagement/ColumnDataTypeConversionSupport.htm
Bryan, should this work is this the correct message or is something else possibly wrong? See below:
Customer is trying the following:
ALTER TABLE adw.dim_prod_bkup ALTER COLUMN otlt_svc_lvl SET DATA TYPE numeric(5,4);
But they are getting this error:
[Code: 2377, SQL State: 0A000] [Vertica]VJDBC ROLLBACK: Cannot convert column "otlt_svc_lvl" from "numeric(3,2)" to type "numeric(5,4)".
Thanks
No, can't change the scale (second digit). You can change the precision (first number) within the ranges allowed, e.g.:
dbadmin=> create table numbers (otlt_svc_lvl numeric(3,2));
CREATE TABLE
dbadmin=> ALTER TABLE numbers ALTER COLUMN otlt_svc_lvl SET DATA TYPE numeric(5,4);
ROLLBACK 2377: Cannot convert column "otlt_svc_lvl" from "numeric(3,2)" to type "numeric(5,4)"
dbadmin=> ALTER TABLE numbers ALTER COLUMN otlt_svc_lvl SET DATA TYPE numeric(5,2);
ALTER TABLE
Shouldn't this be documented in our documentation post 6.1 docs? Thanks
It is, but not directly on the alter table/alter column page. You have to follow the link to the data type conversion page a few posts back. Ask the doc team to update the alter table/alter column page if you think we should make this clear there.
@Bryan_H Thanks for the validation.