how to Add sort key to table column with alter com

Question

i want to add sort key to a column in a table but the table is created. so is there any command by which we can add sort key to the column(field).

Thanks in advance.

Answer 1

I followed this approach for adding the sort columns to my table table_transactons its more or less same approach only less number of commands.

1) alter table table_transactions rename to table_transactions_backup; 2) create table table_transactions compound sortkey(key1, key2, key3, key4) as select * from table_transactions_backup; 3) drop table table_transactions_backup;

Answer 2

As Yaniv Kessler mentioned, it's not possible to add or change distkey and sort key after creating a table, and you have to recreate a table and copy all data to the new table. You can use the following SQL format to recreate a table with a new design.

ALTER TABLE test_table RENAME TO old_test_table;
CREATE TABLE new_test_table([new table columns]);
INSERT INTO new_test_table (SELECT * FROM old_test_table);
ALTER TABLE new_test_table RENAME TO test_table;
DROP TABLE old_test_table;

In my experience, this SQL is used for not only changing distkey and sortkey, but also setting the encoding(compression) type.

Answer 3

To add to Yaniv's answer, the ideal way to do this is probably using the CREATE TABLE AS command. You can specify the distkey and sortkey explicitly. I.e.

CREATE TABLE test_table_with_dist 
distkey(field) 
sortkey(sortfield) 
AS 
select * from test_table

Additional examples:

http://docs.aws.amazon.com/redshift/latest/dg/r_CTAS_examples.html

EDIT

I've noticed that this method doesn't preserve encoding. Redshift only automatically encodes during a copy statement. If this is a persistent table you should redefine the table and specify the encoding.

create table test_table_with_dist(
    field1 varchar encode row distkey
    field2 timestam pencode delta sortkey);

insert into test_table select * from test_table;

You can figure out which encoding to use by running analyze compression test_table;

Answer 4

At the moment I think its not possible (hopefully that will change in the future). In the past when I ran into this kind of situation I created a new table and copied the data from the old one into it.

from http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html:

ADD [ COLUMN ] column_name Adds a column with the specified name to the table. You can add only one column in each ALTER TABLE statement.

You cannot add a column that is the distribution key (DISTKEY) or a sort key (SORTKEY) of the table.

You cannot use an ALTER TABLE ADD COLUMN command to modify the following table and column attributes:

UNIQUE

PRIMARY KEY

REFERENCES (foreign key)

IDENTITY

The maximum column name length is 127 characters; longer names are truncated to 127 characters. The maximum number of columns you can define in a single table is 1,600.