CREATE TABLE LIKE another table but with additional columns

Question

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,...

Answer 1

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

Answer 2

You can use the CREATE TABLE AS <query> option, as detailed in the documentation.

--Code not tested in PostgreSQL--

    CREATE TABLE MyNewTable
    AS
    SELECT *, CAST (NULL AS INT) AS IntCol1, CAST( NULL AS VARCHAR(10)) AS StrCol2...
    FROM MyOriginalTable 
     WITH NO DATA;

Answer 3

No, there is no more succinct method of doing it. But ALTER TABLE can accept multiple arguments,

ALTER TABLE foo
  ADD column bar int,
  ADD column baz int;

Really if your goal is succinct, SQL is almost never an ideal language.