Re: [NOVICE] 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.

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

> [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 Tue, 2007-07-31 at 12:19 +0800, Ronald Rojas wrote:
> 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.

It isn't possible. Nor is it really necessary, since you can ask for
columns in any order in a SELECT.

If you really want to do it, you need to dump the table to a new table
with the columns in the desired order; delete the old table and rename
the new one.

Alternatively, add the new column (which will come at the end), add a
new address column and set its contents to be the same as the current
address column, finally drop the old address column. (This will waste
space in the table.)

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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.

On 31/07/07, Ronald Rojas <ronald(dot)rojas(at)gmail(dot)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 Glaesemanngrzm 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.

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