amazon-web-servicesduplicatescopyamazon-redshift

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.

Solution

  • According to the docs you can do 

    CREATE TABLE my_table(LIKE my_old_table);