How to Duplicate a Table in Redshift

You can duplicate or “clone” a Redshift table’s contents by executing a CREATE TABLE ... AS SELECT statement:

CREATE TABLE new_table AS SELECT * FROM original_table;

Please be careful when using this to clone big tables. This can take a lot of time and server resources.

Note also that new_table inherits ONLY the basic column definitions, null settings and default values of the original_table. It does not inherit table attributes.

To inherit all table definitions, use the CREATE TABLE... LIKE syntax:

CREATE TABLE new_table (LIKE original_table);

This makes the structure of new_table exactly like that of original_table, but does not copy the data. To copy the data, you’ll need INSERT ... SELECT:

INSERT INTO new_table SELECT * FROM original_table;

Again, be careful when doing this to big tables.