From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-docs@postgresql.org
Subject: ALTER TABLE x ALTER COLUMN y TYPE z
Date: Wed, 22 Mar 2006 11:58:13 -0600
Message-ID: <44213BD5.EE98.0025.0@wicourts.gov> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: Kevin.Grittner@wicourts.gov
Subject: Re: ALTER TABLE x ALTER COLUMN y TYPE z
In-Reply-To: <44213BD5.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox