While you have the ability to arbitrarily add new columns to existing tables, remember that (as of PostgreSQL
7.1.x)
you cannot drop columns from existing tables
. There are two fairly painless
workarounds for restructuring existing tables. The first involves the CREATE TABLE AS
command, while the second combines the CREATE TABLE command with the
INSERT INTO command.
Each of these methods, in essence, involves creating a new table with your desired structure, filling it up with the
data from your existing table, and renaming the tables so that the new table takes the place of your old table.
One common technique of restructuring a table is to use the CREATE TABLE command in
conjunction with the AS clause and a valid SQL query. This allows you to restructure your
existing table into a temporary table, which can then be renamed. Doing this also 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.
The following syntax describes this limited version of CREATE TABLE, where
query
is the valid SELECT statement that selects the data
to populate the new table with. The data type of each created column is implied by the type of each corresponding column selected
by
query
:
CREATE [ TEMPORARY | TEMP ] TABLE
table
[ (
column_name
[, ...] ) ]
AS
query
The advantage to this technique is that you may create the new table and populate it in a single SQL
command. The most notable limitation of this technique is that there is no comprehensive way to set constraints
on the newly created table; the only constraints that may be added to the table after is has been created are the foreign
key and check constraints. Once the new table has been created, the old one can be renamed (or destroyed), and the new one can be
renamed to the name of 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, and dropping the old table with DROP TABLE,
as shown in Example 4-14.
Example 4-14. Restructuring 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_id_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 |
booktown=#
DROP TABLE books;
DROP
|
Warning
|
|
As of PostgreSQL 7.1.x, if you specify the optional column list within parentheses, you cannot use the
asterisk (*) in the
query
statement. This behavior is scheduled to be
corrected in PostgreSQL 7.2.
|
If you require a more specifically defined table than that created by 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. You can achieve this 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-15. Restructuring 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 about using
the INSERT INTO command with a SELECT statement,
and the Section called Retrieving Rows with SELECT
" for more information about valid SELECT statements.
