Skip to main content

ERROR: String of 12 octets is too long for type Varchar(10)

  • June 1, 2015
  • 4 replies
  • 17 views
tangiblegeek

Hi,

 

The problem is that when I try to insert a varchar value of length more than the actual defined length of the column, Vertica throws an error.

 

For example: Inserting "DUMMY_INSERT" into a column of length VARCHAR(10)

Here DUMMY_INSERT is of 12 characters.

 

Sybase IQ automatically trims the string value to the limit defined in the table i.e. 10 characters. So when i perform the same insert operation in Sybase it would insert "DUMMY_INSE" into the same column.

 

Can I achieve similar functionality in Vertica where the data gets auto-trimmed and gets inserted?? I dont wish to manually use TRIM or substring everywhere to trim the data.

 

Any inputs would be much appreciated.

 

 

Thanks.

    4 replies

    NorbertK
    Forum|alt.badge.img
    • NorbertK
    • Participating Frequently
    • June 2, 2015

    I would just make the column larger so that you don't run into this issue. There's no cost for unused bytes of a variable character type.

    Sharon_Cutter
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • June 3, 2015
    NorbertK wrote:

    I would just make the column larger so that you don't run into this issue. There's no cost for unused bytes of a variable character type.

     

    Actually, there is a cost for unused bytes in varchars.  Not in projection storage, but during run-time.  I've seen references to engineering working on this, but I don't believe that it's been resolved in any current version of Vertica.  

     

    Especially if you are joining or grouping on varchars, you want the varchar width to be as small as possible, not over-declared.  A few bytes won't make a big difference, but 256 or 4000 characters for 32-character values will.

     

      --Sharon

     

    NorbertK
    Forum|alt.badge.img
    • NorbertK
    • Participating Frequently
    • June 6, 2015

    Thanks, Sharon. I stand corrected. 

     

    I'd like to test this as I'm interested to see what how much performance is affected in those scenarios. 

    tangiblegeek
    • tangiblegeek
    • Author
    • Participating Frequently
    • August 4, 2016

    Hi,

     

    Adding to the same trail, this error gives the data type and the precision of the column but it does not give the column name.

     

    Is there a way to get the column name in the error also(directly from Vertica) or is that not possible in the current versions??

     

    I am asking for this as, I have a customer_details table which has a lot of varchar columns of the same length. So I have to manually check each of the individual columns from the source and then match the data's length. 

     

    I am just looking for a helper feature, if there is one already which I am not aware of.

     

    Thanks,

    Abhilash

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement