Trimming Excess Data In Column Ingestion

Hi,

 

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. 

 

Thanks!

Comments

  • SruthiASruthiA Vertica Employee Administrator

    Hi,

     

        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;
     col1
    ------
    (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);
     OUTPUT
    --------
          1
    (1 row)

     

    dbadmin=> select * from test;
     col1
    ------
     AS
    (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.

     

    -Regards,

     Sruthi

Leave a Comment

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