Re: Altering a table - positioning new columns

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