Обсуждение: 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 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.