Cannot rename column X which belongs to a clustering key-CodePudding

I have defined a table like this:

create or replace TABLE TEST_TABLE  cluster by LINEAR(ARTICLE, ORDER_DATE) (
    ORDER_DATE          DATE              
    ARTICLE             VARCHAR(1555) 
    note                VARCHAR(1555)
);

If I try to rename the column ORDER_DATE, I get an error that it cannot be renamed since it belongs to a clustering key. There is data inside this table that I do not want to get rid of. It is also not convenient to create a new table and copy the entire data into it since there is a lot of data.

Is there any way to temporarily remove the clustering key, rename it and add the key again?

or is there a way to do use a single statement that renames the column and changes the clustering col name at the same time?

CodePudding user response：

you cannot rename a column which is a part of cluster key, one option is to recreate the table with the new column name or create the table with out a cluster key and rename the column.

create or replace TABLE TEST_TABLE  --cluster by LINEAR(ARTICLE, ORDER_DATE)
(
    ORDER_DATE          DATE ,             
    ARTICLE             VARCHAR(1555) ,
    note                VARCHAR(1555)
);

alter table TEST_TABLE rename column ARTICLE to ARTICLE_new;
alter table TEST_TABLE rename column ORDER_DATE to ORDER_DATE_new;

CodePudding user response：

You could create and replace the table with desired column names first (without the clustering key) and then alter the table to add the clustering keys

create or replace table t copy grants as

select order_date as order_date_new, article, note
from t;

alter table t cluster by (article,order_date_new);