Thread: alter table table add column

[Please don't post the same question to many lists. Choose one. If
you're unsure if it's the correct list for your question, ask if
there's a more appropriate one. This question is find for -novice or -
general. Thanks.]

On Jul 30, 2007, at 23:19 , Ronald Rojas wrote:

> And, I want to add the field name age with type integer after
> lastname OR before the address field. How to I do that?

Can't without dumping the database, altering the schema in the dump,
and reloading. But why does it matter? Just call the columns in the
order you want.

Michael Glaesemann
grzm seespotcode net

[Please don't post the same question to many lists. Choose one. If  
you're unsure if it's the correct list for your question, ask if  
there's a more appropriate one. This question is find for -novice or - 
general. Thanks.]

On Jul 30, 2007, at 23:19 , Ronald Rojas wrote:

> And, I want to add the field name age with type integer after  
> lastname OR before the address field. How to I do that?

Can't without dumping the database, altering the schema in the dump,  
and reloading. But why does it matter? Just call the columns in the  
order you want.

Michael Glaesemann
grzm seespotcode net

==================================================================================================
Ronald Rojas
Systems Administrator
Linux Registered  User #427229
==================================================================================================

Arnold's Laws of Documentation:(1) If it should exist, it doesn't.(2) If it does exist, it's out of date.(3) Only documentation for useless programs transcends the    first two laws.

On Jul 31, 2007, at 0:23 , Ronald Rojas wrote:

> Yes I know that procedure but I would like to insert in between
> because I have at third party software that will call the said
> schema and one of its dependencies with the mapping is it should
> have the correct order with what the receiving end will be use for
> the mapping. And in this case, I can't modify the receiving
> structure (third-party) and the tables that I will be using is in
> on production state. So would only mean that I have to schedule a
> very fast maintenance, probably 5 to 10 secs just to re-initialize
> the tables.

Another option would be to use views to change the column order,
which would work for selects. I believe you could create rules for
insert and update as well, if necessary. Perhaps this would be a
solution to your problem.

Michael Glaesemann
grzm seespotcode net

On Jul 31, 2007, at 0:23 , Ronald Rojas wrote:

> Yes I know that procedure but I would like to insert in between  
> because I have at third party software that will call the said  
> schema and one of its dependencies with the mapping is it should  
> have the correct order with what the receiving end will be use for  
> the mapping. And in this case, I can't modify the receiving  
> structure (third-party) and the tables that I will be using is in  
> on production state. So would only mean that I have to schedule a  
> very fast maintenance, probably 5 to 10 secs just to re-initialize  
> the tables.

Another option would be to use views to change the column order,  
which would work for selects. I believe you could create rules for  
insert and update as well, if necessary. Perhaps this would be a  
solution to your problem.

Michael Glaesemann
grzm seespotcode net

==================================================================================================
Ronald Rojas
Systems Administrator
Linux Registered  User #427229
==================================================================================================

Arnold's Laws of Documentation:(1) If it should exist, it doesn't.(2) If it does exist, it's out of date.(3) Only documentation for useless programs transcends the    first two laws.

Ronald Rojas skrev:
> Hi,
>
> Anybody knows how to add column with reference to BEFORE or AFTER any
> given column? Let say here's my table structure:
>
>   Column  |       Type        | Modifiers
> ----------+-------------------+-----------
>  surname  | character varying |
>  lastname | character varying |
>  address   | character varying |
>
> And, I want to add the field name age with type integer after lastname
> OR before the address field. How to I do that?
>
> I would really appreciate your response.

Not tested.

ALTER TABLE foo ADD COLUMN age integer
ALTER TABLE foo ADD COLUMN address2 character varying;
UPDATE TABLE foo SET address2=address;
ALTER TABLE foo DROP COLUMN address;
ALTER TABLE foo RENAME COLUMN address2 TO address;


Nis