add column specify position

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:

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 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.