pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAdd column and specify the column position in a table
6+ messages / 5 participants
[nested] [flat]
* Add column and specify the column position in a table
@ 2006-05-17 21:22 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Emi Lu @ 2006-05-17 21:22 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am trying to insert one column to a specific position in a table.
In mysql, I can do:
. create table test(id varchar(3), name varchar(12));
. alter table test add column givename varchar(12) after id;
I am looking for similar things in postgresql to add a new column to the
correct position in a table.
Could someone hint me please.
Thanks alot!
Ying Lu
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add column and specify the column position in a table
@ 2006-05-18 05:53 Guillaume LELARGE <guillaume.lelarge@gmail.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume LELARGE @ 2006-05-18 05:53 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
Emi Lu a écrit :
> I am trying to insert one column to a specific position in a table.
>
> In mysql, I can do:
> . create table test(id varchar(3), name varchar(12));
> . alter table test add column givename varchar(12) after id;
>
>
> I am looking for similar things in postgresql to add a new column to the
> correct position in a table.
>
> Could someone hint me please.
>
There's no similar thing in PostgreSQL. You have to duplicate the table
to do it. You can do it in a transaction :
CREATE TABLE test (id varchar(3), name varchar(12));
then later :
BEGIN;
ALTER TABLE test RENAME TO oldtest;
CREATE TABLE test (id varchar(3), givename varchar(12), name varchar(12));
INSERT INTO test (id, name) SELECT id, name FROM oldtest;
DROP TABLE oldtest;
COMMIT;
Not really interesting if you have really big tables but, in fact, you
shouldn't rely on columns' order.
Regards.
--
Guillaume.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add column and specify the column position in a table
@ 2006-05-18 14:00 Alvaro Herrera <alvherre@commandprompt.com>
parent: Guillaume LELARGE <guillaume.lelarge@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2006-05-18 14:00 UTC (permalink / raw)
To: Guillaume LELARGE <guillaume.lelarge@gmail.com>; +Cc: emilu@encs.concordia.ca; pgsql-sql
Guillaume LELARGE wrote:
> Emi Lu a écrit :
> > I am trying to insert one column to a specific position in a table.
> >
> > In mysql, I can do:
> > . create table test(id varchar(3), name varchar(12));
> > . alter table test add column givename varchar(12) after id;
> >
> >
> > I am looking for similar things in postgresql to add a new column to the
> > correct position in a table.
>
> There's no similar thing in PostgreSQL. You have to duplicate the table
> to do it.
... which is the same thing MySQL does, only you must do it explicitely.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add column and specify the column position in a table
@ 2006-05-18 15:43 Guillaume Lelarge <guillaume.lelarge@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2006-05-18 15:43 UTC (permalink / raw)
To: Guillaume LELARGE <guillaume.lelarge@gmail.com>; emilu@encs.concordia.ca; pgsql-sql
2006/5/18, Alvaro Herrera <alvherre@commandprompt.com>:
> Guillaume LELARGE wrote:
> > Emi Lu a écrit :
> > > I am trying to insert one column to a specific position in a table.
> > >
> > > In mysql, I can do:
> > > . create table test(id varchar(3), name varchar(12));
> > > . alter table test add column givename varchar(12) after id;
> > >
> > >
> > > I am looking for similar things in postgresql to add a new column to the
> > > correct position in a table.
> >
> > There's no similar thing in PostgreSQL. You have to duplicate the table
> > to do it.
>
> ... which is the same thing MySQL does, only you must do it explicitely.
>
Do you mean that, using "alter table test add column" with the "after"
option, MySQL creates a new table, populates it with the old table
data and finally drops the old table ? I mean, there's the same
performance problem with big tables ?
--
Guillaume.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add column and specify the column position in a table
@ 2006-05-18 15:58 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Guillaume Lelarge <guillaume.lelarge@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Sullivan @ 2006-05-18 15:58 UTC (permalink / raw)
To: pgsql-sql
On Thu, May 18, 2006 at 05:43:19PM +0200, Guillaume Lelarge wrote:
> Do you mean that, using "alter table test add column" with the "after"
> option, MySQL creates a new table, populates it with the old table
> data and finally drops the old table ? I mean, there's the same
> performance problem with big tables ?
MySQL does that for a great deal of its DDL. Yes, the performance is
awful for this on big tables.
The reason I didn't answer the OP's question, though, is that I can't
think of a legitimate reason to do this anyway. The physical layout
of the columns should not be of concern to the developer, who should
be naming the columns anyway.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add column and specify the column position in a table
@ 2006-05-18 16:54 Guillaume Lelarge <guillaume.lelarge@gmail.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Guillaume Lelarge @ 2006-05-18 16:54 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
2006/5/18, Andrew Sullivan <ajs@crankycanuck.ca>:
> On Thu, May 18, 2006 at 05:43:19PM +0200, Guillaume Lelarge wrote:
> > Do you mean that, using "alter table test add column" with the "after"
> > option, MySQL creates a new table, populates it with the old table
> > data and finally drops the old table ? I mean, there's the same
> > performance problem with big tables ?
>
> MySQL does that for a great deal of its DDL. Yes, the performance is
> awful for this on big tables.
>
> The reason I didn't answer the OP's question, though, is that I can't
> think of a legitimate reason to do this anyway. The physical layout
> of the columns should not be of concern to the developer, who should
> be naming the columns anyway.
>
I totally agree on the physical layout. Just being curious :)
--
Guillaume.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-05-18 16:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-17 21:22 Add column and specify the column position in a table Emi Lu <emilu@encs.concordia.ca>
2006-05-18 05:53 ` Guillaume LELARGE <guillaume.lelarge@gmail.com>
2006-05-18 14:00 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-05-18 15:43 ` Guillaume Lelarge <guillaume.lelarge@gmail.com>
2006-05-18 15:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-05-18 16:54 ` Guillaume Lelarge <guillaume.lelarge@gmail.com>
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