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'
booktown=# INSERT INTO new_books
booktown-#             SELECT id, title, author_id, subject_id
booktown-#                    FROM books;
booktown=# ALTER TABLE books RENAME TO old_books;
booktown=# ALTER TABLE new_books RENAME TO books;
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.