How to add a sort key to an existing table in AWS Redshift

amazon-redshift
jpdave picture jpdave · Jul 26, 2013 · Viewed 31.4k times · Source

In AWS Redshift, I want to add a sort key to a table that is already created. Is there any command which can add a column and use it as sort key?

Answer

Masashi Miyazaki picture Masashi Miyazaki · Nov 21, 2013

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.

Related questions

Show tables, describe tables equivalent in redshift

I'm new to aws, can anyone tell me what are redshifts' equivalents to mysql commands? show tables -- redshift command describe table_name -- redshift command

Read More
Alter column data type in Amazon Redshift

How to alter column data type in Amazon Redshift database? I am not able to alter the column data type in Redshift; is there any way to modify the data type in Amazon Redshift?

Read More
In Redshift/Postgres, how to count rows that meet a condition?

I'm trying to write a query that count only the rows that meet a condition. For example, in MySQL I would write it like this: SELECT COUNT(IF(grade < 70), 1, NULL) FROM grades ORDER BY id DESC; However, when I …

Read More