how to alter column with Numeric(38,2) to numeric(38,4)

I need to change the decimal value of a column

Comments

  • Recreate table.


    ALTER TABLE (https://my.vertica.com/docs/6.1.x/HTML/index.htm#1295.htm)
    SET DATA TYPE data-type

    • 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.
  • VertiguyVertiguy Administrator
    edited August 2019

    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_HBryan_H Vertica Employee Administrator

    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

  • VertiguyVertiguy Administrator

    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

  • Bryan_HBryan_H Vertica Employee Administrator

    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

  • VertiguyVertiguy Administrator

    Shouldn't this be documented in our documentation post 6.1 docs? Thanks

  • Bryan_HBryan_H Vertica Employee Administrator

    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.

  • VertiguyVertiguy Administrator

    @Bryan_H Thanks for the validation.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file