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

sergey_hsergey_h Vertica Customer

Hi,

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('€');
     LENGTH
    --------
          1
    (1 row)
    
    dbadmin=> select OCTET_LENGTH('€');
     OCTET_LENGTH
    --------------
                3
    (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;
    CREATE TABLE
    
    

    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;
     symbol
    --------
     €
    (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.

Answers

  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file