CODEWITHSUNDEEP
amazon-web-servicesduplicatescopyamazon-redshift
SomeGuyOnAComputer
SomeGuyOnAComputer

Reputation: 6208

How to duplicate a Redshift table schema?

I'm trying to duplicate a Redshift table including modifiers.

I've tried using a CTAS statement and for some reason that fails to copy modifiers like not null

create table public.my_table as (select * from public.my_old_table limit 1);

There also doesn't seem to be a way to alter the table to add modifiers after creating the table which leads me to believe that there isn't a way to duplicate a Redshift table schema except by running the original create table statement vs the CTAS statement.

Upvotes: 10

Views: 18075

Answers (3)

Bob McCormick
Bob McCormick

Reputation: 225

There is now a solution

CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS);

Upvotes: 1

Tharaka Deshan
Tharaka Deshan

Reputation: 1396

Easiest way to clone a table with data would be:

CREATE TABLE cloned_table AS SELECT * FROM original_table;

But it doesn't inherit all the table attributes, but only the basic column definitions.

To clone the table exactly(!) like the original, you have to execute 2 queries. one to create the table schema, next to copy data.

-- this will only create a clone of the table structure
CREATE TABLE cloned_table (LIKE original_table);

-- copying data
INSERT INTO cloned_table SELECT * FROM original_table;

(!)Note: But according to the doc, still it does not inherit primary and foreign key constraints.

Tables created with the LIKE option don't inherit primary and foreign key constraints. Distribution style, sort keys, BACKUP, and NULL properties are inherited by LIKE tables, but you can't explicitly set them in the CREATE TABLE ... LIKE statement.

Upvotes: 1

salient
salient

Reputation: 2486

According to the docs you can do 

CREATE TABLE my_table(LIKE my_old_table);

Upvotes: 21

Related Questions