+1Hi,
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!
+1Hi,
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
Already have an account? Login
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
