If you require a more specifically defined table than that created by the CREATE TABLE AS (e.g., one with column constraints), you can replicate the effect of the CREATE TABLE AS technique by issuing two SQL statements rather than one. This is achieved by first creating the new table as you ordinarily would with CREATE TABLE , and then populating the table with data via the INSERT INTO command and a valid SELECT statement.

Example 4-14. Re-structuring a Table with CREATE TABLE and INSERT INTO

booktown=# CREATE TABLE new_books (
booktown(#        id integer UNIQUE,
booktown(#        title text NOT NULL,
booktown(#        author_id integer,
booktown(#        subject_id integer,
booktown(#        CONSTRAINT books_id_pkey PRIMARY KEY (id)
booktown(# );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey' for table 'new_books'
CREATE
booktown=# INSERT INTO new_books
booktown-#             SELECT id, title, author_id, subject_id
booktown-#                    FROM books;
INSERT 0 12
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
booktown=# \d books
          Table "books"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 title      | text    | not null
 author_id  | integer |
 subject_id | integer |
Index: books_id_pkey

See the section called Inserting values from other tables with SELECT for more information on using the INSERT INTO command with a SELECT statement, and the section called Retrieving Rows with SELECT for more information on valid SELECT statements.