Re: full featured alter table?

> > > > Presentation order should be done at the application level.
>>
> > I agree.
> > Use a VIEW for the presentation!
>
> Sorry, but I don't fully agree with you. If I have to add a new column
> in a table, this column will appear in the end of the table. What we are
> talking about (as I understand) is to have the possibility to order the
> columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table>
> would use that order to display the columns.

Yes, I understand that, but I don't understand what the benefits would be.

What use is it to have the columns in a defined order when you do (SELECT * FROM table)?

1. In a "normal" app, you would want to know what the data in the column *means*. Adding a column "kexchoklad" to the "customers" table would not be of any good, regardless of it's position relative to other columns. You would never use "SELECT *". You would SELECT only the columns that matter to this specific part of the app.
2. In the case of a report generator: You would probably not want *every* column from "customers" for a report... How can you print a report without knowing how many columns you'll get? without knowing what they contain? what they mean? Where should you print "kexchoklad"? After the customer name? Just before last_years_sales? In this case you would never use SELECT *. You would probably want to join in other tables too.
3. In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a SELECT * could be of any use...!! Do a SELECT *, and print it out just to get an overview of what's in the table.

In case #3: Ok, this is the relevant case. But do you really want to rearrange the table internally just for this special case?? seems like a lot of programming and potential problems just for one very special case... especially since it can be done quick and easy with a view...! Do you even need a view? If you do a quick and ugly report, do you even care about the column position of "kexchoklad"??

Can you tell me an example of a situation when the column position really matters?

> We are not talking of changing columns order for each kind of SQL query.
> I really think that column ordering (ALTER TABLE ...POSITION..) is very
> interesting and will allow users to avoid loosing time when they have to
> create a new temporary table each time they have to add a new column
> inside (not at the end of) a table, and rename the table after deleting
> the old table...

But *why* would anyone care about the position of the column? Except from a cosmetical point of view... Why all the hassle with temp tables and stuff, just to put the column in a specific position in the table definition? Because it looks neat when you do "\d table" in psql??

I think the original poster was talking about the physical layout of the row, and that it would make some selects more efficient. Exactly how that would be accomplished was not explained.
Then someone started talking about the logical layout (in the specific case of SELECT *), and printing reports.
The physical layout should probably be handled by pg internally, without any interference from users.
The logical layout, well... i just can't see why it would matter at all?

/Mattias