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

I personally agree with this.

I put the fields in the create statement in a particular order to help with understanding the design. Now, if the DB
wantsto put them in whatever STORAGE order it wants, fine, as long as it displays in the same order I created it. 

A possible, probably completely non standard solution to both problems would be a COMBINATION of a select list and '*':

SELECT (col1, col2, * ) from TABLE1;

Certain tables are ordered, the rest are just appended in order of definition behind it.

Mattias Kregert wrote:

>>>>>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
canyou print a report without knowing how many columns you'll get? without knowing what they contain? what they mean?
Whereshould you print "kexchoklad"? After the customer name? Just before last_years_sales? In this case you would never
useSELECT *. 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
specialcase?? seems like a lot of programming and potential problems just for one very special case... especially since
itcan 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
careabout 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
hasslewith temp tables and stuff, just to put the column in a specific position in the table definition? Because it
looksneat 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
moreefficient. 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>