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.