On Wed, 18 Jan 2006, David Fetter wrote: > On Wed, Jan 18, 2006 at 04:33:23PM -0800, Stephan Szabo wrote: > > On Wed, 18 Jan 2006, Jim C. Nasby wrote: > > > > > Yeah, this isn't about production code, it's about making life > > > easier on developers. Humans naturally want to group data into > > > natural sets, so for example all the fields dealing with a > > > person's address would appear together. But if you ever have to > > > use ALTER TABLE to add a field you're stuck with that field being > > > at the end of the table. > > > > > > Another consideration is that the best order for people isn't the > > > best order for the database. For example, grouping fields of the > > > same alignment together will save space (and depending on the > > > table that savings can really start to add up). > > > > > > It would definately be nice if the end-user concept of column > > > order wasn't tied to the physical order in the database. > > > > I agree with that. However, I'm not sure that an ALTER TABLE that > > reorders a logical column set is necessarily the right way to handle > > the issue. I think that the same path leads to realizations that a > > single logical ordering may not be sufficient for development. > > > > For example, I could see cases where say person A wants all the > > address columns together but person B only cares about country and > > wants the columns he deals with together in some other fashion. > > Although it might be nice to have different column orderings, say > per-role, the SQL:2003 standard requires a single canonical ordering > in the information schema. How would we handle both? That's part of the problem. I'm not 100% sure where the people who want this are going with the feature. I've heard a lot of talk about tools, but that's potentially solvable without changing the canonical ordering as long as the tools obey not directly using select * internally (if the user asks for it as an sql statement obviously that's what should get done); and it was done in a postgresql specific, but documented way, to allow different tools to use it. If the issue then is that select * doesn't come back in that order, then we get back into the question of does a single ordering make sense? If not, and we allow select * to change, then applications and queries will fail (imagine select * from a union select * from b where different users are reordering a and b separately).
