Column length

Goldengate

Hi guys, I've defined a column with varchar(50) and running my ETL I get the error with ODBC: error code 20 row rejected by server see server log. Thing is one of the rows has a length of 49 characters. Why I can't do the insert?

Regards!

Fran

SruthiA

@Goldengate : This is a known issue. error is very generic. in order to find the exact error message, please check vertica.log. for more information on this issue, please check below link and search for VER-67024

https://www.vertica.com/docs/ReleaseNotes/10.0.x/Vertica_10.0.x_Release_Notes.htm

VER-67024 - Client Drivers - ODBC Previously, most batch insert operations (performed with the ODBC driver) which resulted in the server rejecting some rows presented the user with an inaccurate error message:

Row rejected by server; see server log for details

No such details were actually available in the server log. This error message has been changed to:

Row rejected by server; check row data for truncation or null constraint violations

DaveT

Not sure if this is you issue but it could happen if you have any multi-byte characters. varchar(50) means 50 bytes and not 50 characters.

Goldengate

Thx for the quick answer DaveT & SruthiA. I don't have access to the Linux console of the servers so I couldn't tell the exact error, I've tested changing the column's data to fewer characters and Vertica didn't complain and the data its in the table so DaveT should be right now: How do I provision the column length then? varchar(60)? I need 50 characters for the table.
Thx again guys! you're great!

SruthiA

it is always better to provision column length with little more than size of column in data stream so that it can cover the edge cases like this. However don't set the length to a very high value as column lengths are used while computing memory required for a query. if the values are too high, it is possible that query will use more memory even though it actually doesn't need it.

mosheg

I think all English printable characters can fit in one byte as shown in the VSQL test below.
You can try a similar test on your string.

-- printf '%b' $(printf '\\%03o' {30..127}) | tr -d "'" | wc -c       --->  97
create table tv (f1 varchar(97));
\set MYLINE `printf '%b' $(printf '\\%03o' {30..127}) | tr -d "'"`
\set MYLINE '''':MYLINE''''
\echo :MYLINE
' !"#$%&()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}'
insert into tv (f1) values (:MYLINE);
commit;
select TO_CHAR(BIT_LENGTH(f1)/8,'99') as Byte_length, f1 from tv;
 Byte_length |                                                f1
   -------------+---------------------------------------------------------------------------------------------------
            97         |  !"#$%&()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}