Trimming Excess Data In Column Ingestion



I am trying to insert data into fields that may be too short to accomodate (ex: column is char or varchar of a fixed size, and I am inserting something greater than that size). I was wondering whether Vertica trims the excess? If not, is there a way to have Vertica do so, as it is producing many errors in my ingestion. 




  • Options
    SruthiASruthiA Vertica Employee Administrator



        There is a way to trim excess characters in vertica while inserting data into fields. I created a table test with the following definition


    dbadmin=> \d test
                                       List of Fields by Tables
     Schema | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
     public | test  | col1   | varchar(2) |    2 |         | f        | f           |




    dbadmin=> select * from test;
    (0 rows)


    dbadmin=> insert into test select SUBSTRING('ASD', 0, (select data_type_length from v_catalog.columns where table_name = 'test' and column_name = 'col1')+1);
    (1 row)


    dbadmin=> select * from test;
    (1 row)



    This is one way of trimming characters while inserting data into column fields. Vertica does not trim the excess automatically as of now.




Leave a Comment

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