Column length

GoldengateGoldengate Community Edition User

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?





  • Options
    SruthiASruthiA Vertica Employee Administrator

    @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


    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

  • Options
    DaveTDaveT Vertica Employee Employee

    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.

  • Options
    GoldengateGoldengate Community Edition User

    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!

  • Options
    SruthiASruthiA Vertica Employee Administrator

    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.

  • Options
    moshegmosheg Vertica Employee Administrator

    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);
    select TO_CHAR(BIT_LENGTH(f1)/8,'99') as Byte_length, f1 from tv;
     Byte_length |                                                f1
                97         |  !"#$%&()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file