X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id D084B9DC84C
	for <pgsql-docs-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Mar 2006 13:58:35 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 04312-01
	for <pgsql-docs-postgresql.org@localhost.postgresql.org>;
	Wed, 22 Mar 2006 13:58:34 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id DA7009DCD3D
	for <pgsql-docs@postgresql.org>; Wed, 22 Mar 2006 13:58:32 -0400 (AST)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Wed, 22 Mar 2006 11:58:32 -0600
Message-Id: <44213BD5.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0 
Date: Wed, 22 Mar 2006 11:58:13 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-docs@postgresql.org>
Subject: ALTER TABLE x ALTER COLUMN y TYPE z
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.102 required=5 tests=[AWL=0.102, UPPERCASE_25_50=0]
X-Spam-Score: 0.102
X-Spam-Level: 
X-Archive-Number: 200603/27
X-Sequence-Number: 3484

On this page:

http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html

there is this user comment:

--------------------

To change the data type of a column, do this:

BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab RENAME old_col TO temp_name;
ALTER TABLE tab RENAME new_col TO old_col;
ALTER TABLE tab DROP COLUMN temp_name;
COMMIT;

You might then want to do VACUUM FULL tab to reclaim the disk space
used by the expired rows.

--------------------

The 8.1 release (and the 8.0 release) support the same functionality
with a single line:

ALTER TABLE tab ALTER COLUMN old_col TYPE new_data_type;

I think the user comment should be removed, unless there is some
benefit to using the multi-step process.  If there is some benefit, I
think it should be described, so that users know when to use it instead
of the simpler technique.

-Kevin