SQL: How to create new table with both data from old table and new empty columns

Question

I wish to make a new table with some data from the old, but also with new empty columns I can edit.

I would start with:

CREATE TABLE new_table AS SELECT ID, title, summary FROM old_table;

and then alter the table with new columns:

ALTER TABLE new_table ADD note datatype;

But I need my new empty column to be between title & summary, and not 'in the end' as ALTER gives me.

I would like to combine CREATE TABLE and CREATE TABLE AS SELECT - but is that possible?

Answer 1

What about: CREATE TABLE new_table AS SELECT ID, title, '' AS note, summary FROM old_table;

If you wanted to have a specific datatype, you could try CAST. For example, CAST(1 as INTEGER) AS note