Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Column length

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

Tagged:

Answers

  • SruthiASruthiA Employee

    @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

  • DaveTDaveT 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.

  • 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!

  • SruthiASruthiA Employee

    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.

  • moshegmosheg 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:;<=>[email protected][\]^_`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:;<=>[email protected][\]^_`abcdefghijklmnopqrstuvwxyz{|}
    

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.