Re: full featured alter table? - Mailing list pgsql-general

>   I have been following this thread with great interesting and
> perplexity.  I have yet to understand the reasoning behind this proposed
> addition.  It seems useful only for SELECT * yet most posts say that
> "SELECT *" is bad in an app.  Others say that if SELECT * is used then
> the app has to look for the proper column(s) anyway so ordering is not
> important.  As stated in the parent post from Mattias Kregert (with whom
> I completely agree with), SELECT * is generally always a quick-n-ugly
> check of the table.  Surely us humans can adapt to the column positions
> for checking tables once in a while.  And what if an application,
> expecting a pre-defined order, receives a column in a position that it
> doesn't expect?  Wouldn't it still be better to define the column order
> in the SELECT statement or just look for the column it wants in the
> table information?

I don't want to abled to define the column-order just because my "select
*" would look better - it's just that a "select *" should also show the
defined column-order if there is any.

Defining the column-order is just an organisational task.
It is just like having good identifier names in your program-code or
like tidying up your desk - i don't tidy up my desk that often, but i
want a certain tidiness in my database.

In addition, postgresql doesn't offer anything to change a
column-definition. So although your columns are in the logical order you
like when you create a table, your logical order will be broken if you
add a column that you've forgotton or have to change a columns type by
copying the data to a new column.

In order to do something equivalent to a column definition change (the
stuff this thread was about initially) you have to create a new column
with the desired type, copy data, delete the old column _and_ move the
new column to the place the old column was.

In addition, beeing abled to define the column order is a step into the
direction of a more complete ALTER TABLE command - something the most
DBMS are lacking.
MySQL is abled to insert a column at a certain position, but isn't abled
to re-arange columns - this might be due to the fact, that MySQL only
knows the physical order of the columns. This is something we are not
expecting from postgresql - since physical order doesn't matter much
from the user perspective and might be optimized by postgresql internally.
Having a defined column-order is a good thing (would be a basic
requirement to optimize the physical column-order without modifying the
table layout) and to be abled to modify that defined column ordering is
some kind of service for the user.