Thread: add column specify position
Hi all, Is it possible to specify a position when adding a column to a table? I want to swap one column for another without losing the column's position. eg: given that 'foo' is the 5th column in an 8 column table, I want to replace it with a 'bar' column at column 5. ALTER TABLE qwe DROP COLUMN foo; ALTER TABLE qwe ADD COLUMN bar; I'm writing a sql script to migrate from one version of my schema to another, dropping one column and adding another to a number of tables. Unfortunately, the windowing toolkit I'm using relies on integer values to determine column positions. Any ideas come to mind? Thanks in advance! Scott
Hi Scott Scott Frankel wrote: > > Hi all, > > Is it possible to specify a position when adding a column to a table? No not AFAIK. > I want to swap one column for another without losing the column's > position. eg: given that 'foo' is the 5th column in an 8 column table, > I want to replace it with a 'bar' column at column 5. > > ALTER TABLE qwe DROP COLUMN foo; > ALTER TABLE qwe ADD COLUMN bar; ALTER TABLE qwe RENAME COLUMN foo TO bar; That does imply the COLUMN TYPE stays the same though... The help file does have examples in the ALTER TABLE section how to change the TYPE of a column. HTH, Johan Nel Pretoria, South Africa.
On Wed, Feb 3, 2010 at 4:14 PM, Scott Frankel <frankel@circlesfx.com> wrote:
--
Shoaib Mir
http://shoaibmir.wordpress.com/
Hi all,
Is it possible to specify a position when adding a column to a table?
Not possible, but have a read of http://wiki.postgresql.org/wiki/Alter_column_position and look at the alternative options.
--
Shoaib Mir
http://shoaibmir.wordpress.com/
Excellent! Thanks for providing the link. I think the 'add columns and move data' option would best fit my needs.
Thanks!
Scott
On Feb 2, 2010, at 11:44 PM, Shoaib Mir wrote:
On Wed, Feb 3, 2010 at 4:14 PM, Scott Frankel <frankel@circlesfx.com> wrote:
Hi all,
Is it possible to specify a position when adding a column to a table?Not possible, but have a read of http://wiki.postgresql.org/wiki/Alter_column_position and look at the alternative options.
--
Shoaib Mir
http://shoaibmir.wordpress.com/
On 2010-02-03, Scott Frankel <frankel@circlesfx.com> wrote: > > Hi all, > > Is it possible to specify a position when adding a column to a table? no. > I want to swap one column for another without losing the column's > position. eg: given that 'foo' is the 5th column in an 8 column > table, I want to replace it with a 'bar' column at column 5. > > ALTER TABLE qwe DROP COLUMN foo; > ALTER TABLE qwe ADD COLUMN bar; ALTER TABLE qwe ALTER COLUMN foo TYPE bartype USING NULL; ALTER TABLE qwe RENAME foo TO bar; the first changes the type and sets the value to NULL. the second changes the name. > I'm writing a sql script to migrate from one version of my schema to > another, dropping one column and adding another to a number of > tables. Unfortunately, the windowing toolkit I'm using relies on > integer values to determine column positions. > > Any ideas come to mind? you can use a different expression instead of NULL above if you want to translate the data. if many columns are involved it might be better to rewrite the table using SELECT ... FROM qwe INTO temptable; DROP TABLE qwe; ALTER TABLE temptable RENAME to qwe; you'll probably want to do that inside a transaction.