The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

I can't convert archer(128) to varchar(64)

sergey_hsergey_h Vertica Customer


i try to convert varchar(128) to varchar(64), but i get error "Cannot convert column "test" to type "varchar(64)""
I don't understand why?

I check table:

select length(test), count(*) from etl.test
where length(test) > 64
group by 1
order by 1 desc;

result - null

what could be the problem?

Best Answer

  • LenoyJLenoyJ - Select Field - Employee
    edited April 2021 Answer ✓

    @Jim_Knicely is hinting at why you're likely facing the error. In Vertica, VARCHAR(x) means up to x bytes. So you need to be using OCTET_LENGTH() instead of LENGTH() to compare..

    Quick example using the € symbol:

    The length of € is 1, but its octet length is 3. We need 3 bytes to store one of €.

    dbadmin=> select LENGTH('€');
    (1 row)
    dbadmin=> select OCTET_LENGTH('€');
    (1 row)

    Let's create a table to show the error you got. Creating a table with a varchar of 3 bytes that can store €.

    dbadmin=> create table test(symbol varchar(3)) unsegmented all nodes;

    Inserting € to the table.

    dbadmin=> COPY test FROM STDIN;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    dbadmin=> select symbol from test;
    (1 row)

    Trying to change varchar size from 3 bytes to 1 byte and you hit the error you got.

    dbadmin=> alter table test alter column symbol set data type varchar(1);
    ROLLBACK 2378:  Cannot convert column "symbol" to type "varchar(1)"
    HINT:  Verify that the data in the column conforms to the new type

    So, use OCTET_LENGTH() to compare.


  • Bryan_HBryan_H Vertica Employee Administrator

    Can you share the exact error? It's likely a constraint violation, e.g.:

    => alter table bryanh.colsize alter column v1 set data type varchar(64);
    ROLLBACK 2353: Cannot alter type of column "v1" since it is referenced in the segmentation expression of projection "colsize_b0"

  • sergey_hsergey_h Vertica Customer

    bi=> alter table etl.test alter column request_var SET DATA TYPE varchar(64);
    ROLLBACK 2378: Cannot convert column "request_var" to type "varchar(64)"
    HINT: Verify that the data in the column conforms to the new type

    bi=> select date_time::date, count(*) from etl.test
    bi-> where length(request_var) > 64
    bi-> group by 1
    bi-> order by 1 desc;
    date_time | count
    (0 rows)

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited April 2021

    What is the current data type of request_var?

    Can you run this?

    SELECT MAX(octet_length(request_va)), MAX(length(request_va)) FROM etl.test;

  • sergey_hsergey_h Vertica Customer

    @Jim_Knicely and @ LenoyJ thanks you very much!

    The request_var is now of type varchar(128)

    bi=> SELECT MAX(octet_length(request_var)), MAX(length(request_var)) FROM etl.test;
     MAX | MAX
     121 |  64
    (1 row)

Leave a Comment

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