Re: Altering a table - positioning new columns

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: Chris Boget <chris(at)wild(dot)net>
Cc: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>, Manfred Koizar <mkoi-pg(at)aon(dot)at>, PGSql General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Altering a table - positioning new columns
Date: 2003-01-20 16:13:22
Message-ID: 3E2C2022.B9B25DAE@nsd.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

After:

Create table t ( f1 int, f2 int, f3 int);
Alter table t add f4 int;

If you do not like the default order, you can:

create table tmp as select f1, f4, f2, f3 from t;
drop table t;
alter table tmp rename to t;

JLL

Chris Boget wrote:
>
> > > Yeah, these are pretty serious drawbacks. It took me 30+ minutes to do
> > > this for just _one_ table. I don't like new fields trailing the column list because
> > > I prefer all similar columns to be grouped. That way if you are viewing the
> > > data through a UI, it's easier to see/read.
> > > But I guess unless I want to spend hours adding a few columns to some
> > > tables, I'm just going to have to learn to live with it... :|
> > Of course that is purely presentation of the data, nothing to do with integrity
> > of the data,
>
> You are absolutely correct.
>
> > therefore just as the order of rows returned from a query are undefined, unless
> > explicitly ordered, so are the columns.
>
> Indeed. "Unless explicitly ordered" is the key phrase. As you can "explicity order"
> the rows in a query, it would be nice if you could "explicity order" the layout of your
> table when altered.
>
> > MySQL might be seen as having a good feature if it can manage the column
> > ordering bit but imo it's fud, detracting from the real job of a DB.
>
> You are correct. The job of the DB is to keep/hold/serve data. However, you can't
> overlook the person managing that data. Especially when it comes to large(ish)
> tables. It make managing the tables/data harder if you have to look all over the
> place for the fields in a table. Unless you create a table to be 100% first time (and
> that never happens), you will need to search all over the place. It is nice if you
> can have all your date fields in one area, flag fields in another as opposed to 3
> date fields in the middle of the table, 2 towards the end and another at the very
> end.
> If you (general "you") have added a bunch of fields to a table, go into phpPGAdmin,
> pgAdmin II (or some other UI) and look at it. You'll see what I mean.
>
> Again, this is something I could learn to live with. But after using mySQL for 4
> years and adding tons of fields to various tables to incorporate new functionality,
> let me tell you how nice it is to be able to place new fields where you want them
> in your table.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Gregory Seidman 2003-01-20 16:14:56 Re: passwords and 7.3
Previous Message Manfred Koizar 2003-01-20 16:05:53 Re: Altering a table - positioning new columns