How to duplicate a Redshift table schema?

Question

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.

Hornbook

Answer 1

According to the docs you can do

CREATE TABLE my_table(LIKE my_old_table);

Avisavitaminosis

that command didn't work but you lead me on the right path. The command that worked is CREATE TABLE my_table(LIKE my_old_table); – Hornbook

This command doesn't copy the data in the table, just the structure/metadata, and not all of the metadata (comments aren't copied, for instance). – Geosynclinal

This command doesn't copy default values or primary/foreign keys. – Typical

Answer 2

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.

Tacita