One common technique to re-structure a table is to use the CREATE TABLE command in conjunction with the AS clause, and a valid SQL query, in order to re-structure your existing table into a temporary location, which can then be re-named. This technique allows you to both remove and re-arrange columns to a table by physically re-creating it, and simultaneously re-populating it with data from the original table.

Suppose, for example, that you wanted to modify the books table in order to drop the superfluous publication column which was created in the section called Adding columns . You can create a limited copy of the table (designating only the desired columns) by passing a valid SELECT statement to the AS clause of CREATE TABLE .

The following syntax describes this limited version of CREATE TABLE , where table is the name of the new table to be created, the grouped list of column_name s are the optional names of the new columns to be created, and query is the valid SELECT statement which selects the data to populate the new table with. The data type is derived from the type of each column selected by query .

  CREATE [ TEMPORARY | TEMP ] TABLE table 
         [ (column_name [, ...] ) ]
         AS query

The convenient advantage to this technique is that you are able to create the new table and populate it in one SQL command. However, the most notable limitation of this technique is that there is no comprehensive way to set constraints on the newly created table; the only constraint which may be added to the table after is has been created is the foreign key constraint. Once the new table has been created, the old one can be re-named (or destroyed), and the new one can be re-named to the name of the original table. This technique is demonstrated in Example 4-13 , but you may wish to read the next section if you require a table with other constraints.

Example 4-13. Re-structuring a Table with CREATE TABLE AS

booktown=# \d books
          Table "books"
  Attribute  |  Type   | Modifier
-------------+---------+----------
 id          | integer | not null
 title       | text    | not null
 author_id   | integer |
 subject_id  | integer |
 publication | date    |
Index: books_pkey
 
booktown=# CREATE TABLE new_books
booktown-#        (id, title, author_id, subject_id)
booktown-#        AS SELECT id, title, author_id, subject_id
booktown-#                  FROM books;
SELECT
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 |
 title      | text    |
 author_id  | integer |
 subject_id | integer |