Re: add column specify position

On 2010-02-03, Scott Frankel <frankel(at)circlesfx(dot)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.