Trimming Excess Data In Column Ingestion

ssheikh

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!

SruthiA

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