Thread: alter table table add column

alter table table add column

From
Ronald Rojas
Date:
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.

Thanks in advance.


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


Re: [NOVICE] alter table table add column

From
Michael Glaesemann
Date:
[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



Re: [NOVICE] alter table table add column

From
Ronald Rojas
Date:
Hi,

Sorry for doing the multiple mailing list recipient. 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.

Anyway, thanks for your input, really appreciate it.


On Mon, 2007-07-30 at 23:32 -0500, Michael Glaesemann wrote:
[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.


Re: [NOVICE] alter table table add column

From
Michael Glaesemann
Date:
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



Re: [NOVICE] alter table table add column

From
Ronald Rojas
Date:
Oh yes you have a good point. But then I will still have to test insert and update on views.

Thanks a lot michael!

On Tue, 2007-07-31 at 00:56 -0500, Michael Glaesemann wrote:
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.


Re: [NOVICE] alter table table add column

From
"Peter Childs"
Date:


On 31/07/07, Ronald Rojas <ronald.rojas@gmail.com> wrote:
Oh yes you have a good point. But then I will still have to test insert and update on views.

Thanks a lot michael!

On Tue, 2007-07-31 at 00:56 -0500, Michael Glaesemann wrote:
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


But really you should not be using select * from ... anyway,
Always list your column names, That way you will aways get the columns in the order you want rather than in the order they are stored.

This really belongs in a FAQ

Peter.

Re: alter table table add column

From
Nis Jørgensen
Date:
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