Обсуждение: alter table table add column
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.
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. |
[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
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:
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. |
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
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:
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. |
On 31/07/07, Ronald Rojas <ronald.rojas@gmail.com> wrote:
But really you should not be using select * from ... anyway, 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
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.
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