The syntax of Create Table As SQL statement is:
CREATE TABLE table_name [ ( column_name [, ...] ) ] AS select [ * ! ( column_name [, ...] ) ] FROM source_table_name
Replace table_name with the name of the new table that will be created. Column name is optional, where you can specify multiple columns by including their names in a comma-delimited list. Else, the structure of the new table will be based on the column names, types and number of columns returned by the Select statement, together with the row data. If you specify the column name, note that there should be the same number of columns specified as are returned by select.
The select statement at the end of create table as command must be valid, and has the number of targets selected matching the number of columns in the optional column list preceding the AS clause. It can be a complex select statement that retrieve data from multiple tables. If optional column list is specified within parentheses, asterisk (*) can no longer be used in the select statement.
CREATE TABLE demo_backup AS SELECT * FROM demo;
Above SQL statement will create a exact replica backup table named demo_backup with data and structure (columns) of demo table.
Possible error if you specify the optional column list is:
ERROR: CREATE TABLE/AS SELECT has mismatched column count
If you encounter this error message, this is due to optional list of columns in parentheses contains a different number of rows than the select statement returns. Double check if the number of columns specified is the same with the results that are expected from the select resultset.