Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Restructuring Existing Tables

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.

Warning

When "restructuring" a table in this fashion, it is important to notice that old indices placed on the original table will not automatically be applied to the newly created table, nor will the OIDs (object identifiers) be the same. Any indices must be dropped and recreated.

Restructuring with CREATE TABLE AS

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.

Restructuring with CREATE TABLE and INSERT INTO

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire