Thread: ALTER TABLE x ALTER COLUMN y TYPE z
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
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > On this page: > http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html > ... > I think the user comment should be removed, unless there is some > benefit to using the multi-step process. There isn't, and I agree it should go. The comment seems to have been attached to both 7.4 and 8.1 at the same time --- it is useful for 7.4, but not later versions. regards, tom lane
On Wednesday 22 March 2006 12:58, Kevin Grittner wrote: > 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. > I believe Tom's comments in this email apply similarly here. http://archives.postgresql.org/pgsql-general/2006-03/msg00891.php Feel free to submit an additional doc comment. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>>> On Wed, Mar 22, 2006 at 1:00 pm, in message <200603221400.33513.xzilla@users.sourceforge.net>, Robert Treat <xzilla@users.sourceforge.net> wrote: > > I believe Tom's comments in this email apply similarly here. > http://archives.postgresql.org/pgsql- general/2006- 03/msg00891.php The user comment's recommended technique includes this line: ALTER TABLE tab DROP COLUMN temp_name; Would this cause a table rewrite? (Not a rhetorical question. I really don't know.) > Feel free to submit an additional doc comment. I did, but it was rejected -- presumably because it included a question. Once I have a better handle on the issue, if it seems like it needs it, I'll try again. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> The user comment's recommended technique includes this line:
> ALTER TABLE tab DROP COLUMN temp_name;
> Would this cause a table rewrite? (Not a rhetorical question. I
> really don't know.)
No, it wouldn't. The UPDATE is the only part that modifies the table
contents.
However, the arguments Robert pointed to had to do with concurrent
access to the table, and somehow I am not seeing the use-case for ALTER
COLUMN TYPE on a table that's being actively used by other clients.
It seems unlikely that you could do that without needing to also update
your client software. I'd be a tad worried about stale-cached-plan
problems too, in current PG releases. Finally, since the first ALTER
will take an exclusive lock that won't be released until COMMIT, this
approach doesn't avoid the problem of holding exclusive lock for a long
time.
regards, tom lane
On Wednesday 22 March 2006 15:14, Tom Lane wrote:
> However, the arguments Robert pointed to had to do with concurrent
> access to the table, and somehow I am not seeing the use-case for ALTER
> COLUMN TYPE on a table that's being actively used by other clients.
> It seems unlikely that you could do that without needing to also update
> your client software.
Altering between char and text or some such? Or something like INET to TEXT.
(Not that it matters for the things being discussed here, but I think there
are cases)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL