7

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

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

| improve this 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.

| improve this answer | |
3

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.