I’m a big fan of Stack Overflow and I tend to contribute regularly (am currently in the top 0.X%). In this category (stackoverflow) of posts I will will be posting my top rated questions and answers. This, btw, is allowed as explained in the meta thread here.

So, my question was as follows:

I know I can do something like this:

CREATETABLE new_table AS(SELECT field1, field2, field3
    FROM my_table
)

I’m wondering how do I add more columns to this create table SQL, that are not from my_table, but instead ones that I would write my self and which would be unique to this new_table only.

I know I could just make the table with the above SQL and then additionaly (after the command is completed) add the necessary columns, but am wondering if this all could be done in one command, maybe something like this (tried it like that, but didn’t work):

CREATETABLE new_table AS((SELECT field1, field2, field3
    FROM my_table),
    additional_field1 INTEGER NOTNULLDEFAULT1,
    additional_field2 VARCHAR(20)NOTNULLDEFAULT1)

The answer, by user Omesh, was this:

You can also explicitly specify the data type for a generated column:

See Create Table Select Manual

CREATETABLE new_table
(
 additional_field1 INTEGER NOTNULLDEFAULT1,
 additional_field2 VARCHAR(20)NOTNULLDEFAULT1)AS(SELECT id, val,1AS additional_field1,1AS additional_field2
 FROM my_table
);

Example: SQLFiddle