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

sergey_h

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?

LenoyJ

@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.

Bryan_H

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_h

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_Knicely

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_h

@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)