pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedalter table table add column
8+ messages / 5 participants
[nested] [flat]
* alter table table add column
@ 2007-07-31 04:19 Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 04:32 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 06:25 ` Re: alter table table add column Oliver Elphick <olly@lfix.co.uk>
2007-07-31 07:56 ` Re: alter table table add column Nis Jørgensen <nis@superlativ.dk>
0 siblings, 3 replies; 8+ messages in thread
From: Ronald Rojas @ 2007-07-31 04:19 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-admin@postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [NOVICE] alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
@ 2007-07-31 04:32 ` Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 05:23 ` Re: [NOVICE] alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Michael Glaesemann @ 2007-07-31 04:32 UTC (permalink / raw)
To: Ronald Rojas <ronald.rojas@gmail.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org; pgsql-novice@postgresql.org; pgsql-admin@postgresql.org
[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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [NOVICE] alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 04:32 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
@ 2007-07-31 05:23 ` Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 05:56 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 8+ messages in thread
From: Ronald Rojas @ 2007-07-31 05:23 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [NOVICE] alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 04:32 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 05:23 ` Re: [NOVICE] alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
@ 2007-07-31 05:56 ` Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 06:37 ` Re: [NOVICE] alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Glaesemann @ 2007-07-31 05:56 UTC (permalink / raw)
To: Ronald Rojas <ronald.rojas@gmail.com>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [NOVICE] alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 04:32 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 05:23 ` Re: [NOVICE] alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 05:56 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
@ 2007-07-31 06:37 ` Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 07:54 ` Re: [NOVICE] alter table table add column Peter Childs <peterachilds@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ronald Rojas @ 2007-07-31 06:37 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [NOVICE] alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 04:32 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 05:23 ` Re: [NOVICE] alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 05:56 ` Re: [NOVICE] alter table table add column Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 06:37 ` Re: [NOVICE] alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
@ 2007-07-31 07:54 ` Peter Childs <peterachilds@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Childs @ 2007-07-31 07:54 UTC (permalink / raw)
To: ; +Cc: pgsql-general@postgresql.org
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 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
@ 2007-07-31 06:25 ` Oliver Elphick <olly@lfix.co.uk>
2 siblings, 0 replies; 8+ messages in thread
From: Oliver Elphick @ 2007-07-31 06:25 UTC (permalink / raw)
To: pgsql-novice <pgsql-novice@postgresql.org>
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@lfix.co.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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: alter table table add column
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
@ 2007-07-31 07:56 ` Nis Jørgensen <nis@superlativ.dk>
2 siblings, 0 replies; 8+ messages in thread
From: Nis Jørgensen @ 2007-07-31 07:56 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-07-31 07:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-31 04:19 alter table table add column Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 04:32 ` Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 05:23 ` Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 05:56 ` Michael Glaesemann <grzm@seespotcode.net>
2007-07-31 06:37 ` Ronald Rojas <ronald.rojas@gmail.com>
2007-07-31 07:54 ` Peter Childs <peterachilds@gmail.com>
2007-07-31 06:25 ` Oliver Elphick <olly@lfix.co.uk>
2007-07-31 07:56 ` Nis Jørgensen <nis@superlativ.dk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox