HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

CREATE TABLE LIKE another table but with additional columns

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
createcolumnswithbutlikeanothertableadditional

Problem

Is there a way to create a table that has the same structure as another table, but with additional columns?
From this question, I can copy a table's structure with:

CREATE TABLE new_table_name ( like old_table_name including all)


And then I can add columns one at a time, with ALTER TABLE new_table_name ADD COLUMN... but I am looking for a way to do this more succinctly, like:

CREATE TABLE new_table_name ( like old_table_name including all), new_col1 new_col1_type, new_col2 new_col2_type,...

Solution

You can use the syntax below;

CREATE TABLE old_table_name (
  id serial,
  my_data text,
  primary key (id)
);

CREATE TABLE new_table_name ( 
  like old_table_name including all,
  new_col1 integer, 
  new_col2 text

);


The fiddle is here

Code Snippets

CREATE TABLE old_table_name (
  id serial,
  my_data text,
  primary key (id)
);

CREATE TABLE new_table_name ( 
  like old_table_name including all,
  new_col1 integer, 
  new_col2 text

);

Context

StackExchange Database Administrators Q#207097, answer score: 36

Revisions (0)

No revisions yet.