alter diststyle in an existing table in redshift. is it possible?

Question

instead of dropping a table and recreating it as diststyle all I would wish to alter the table's diststyle, is that possible?

Thanks!

Answer 1

The easiest way I've found is to use the following:

BEGIN;

CREATE TABLE mytable_tmp
DISTSTYLE ALL -- You can also use DISTKEY(some_column) or DISTSTYLE EVEN
AS SELECT * FROM mytable;

DROP TABLE mytable;
ALTER TABLE mytable_tmp RENAME TO mytable;

COMMIT;

This allows you to easily modify the distkey or diststyle of a table without even knowing what columns are in that table. You only need to know the table's name.

Answer 2

Nowadays you can use ALTER TABLE table_name ALTER DISTSTYLE KEY DISTKEY column_name, it should be followed up by VACUUM SORT ONLY table_name

see docs for more details. the table will be redistributed on the clusters:

When specifying DISTSTYLE KEY, the data is distributed by the values in the DISTKEY column. For more information about DISTSTYLE, see ...

Answer 3

Doesn't appear to be possible. Easier to recreate the table populating from the old one.