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

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.

NorbertK

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

---

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

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

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