Re: [GENERAL] Reordering columns, will this ever be simple?
On 13/08/07, novnov <[EMAIL PROTECTED]> wrote: > > > I would like to make a request for this feature to be added to postgres. > > Postgres is a really great database. I'm still very much a novice at using > postgres but in general, it's been a very good experience and I plan to > use > it as often as I can. The community is very helpful. > > My projects tend to be more seat of the pants than your average enterprise > database. Adding the ability to reorder columns in a simple manner would > be > a very nice addition. I think such a feature would add to the 'curb > appeal' > of postgres...make it more user friendly, more flexible. > > I don't know anything about the internals of databases, how they actually > work, but from my 'ignorance is bliss' perspective, it seems odd that one > can alter field names at will in postgres but reordering columns is such a > big deal. Maybe internally columns are id'd via index position, thus order > matters, and the name is relatively light duty property? Maybe too hard to > explain it in simple terms. Yes thats it or nearer enough, It was not until quite recently that you could even drop a column. Even now dropping a column is not a very good idea, It leaves the column around inside the database marked as deleted data still intact (if I remember correctly) So you can even get the data back by hmm messing with the database internals (not a very good idea I believe an update on pg_attribute will do the trick:)). Hence if you do more than drop the odd column here or there you may be better dumping and reloading anyway! Further more if you then add another column the already dropped column does not get recycled. I also remember some 7.2 drivers not coping correctly with 7.3 due to dropped columns. Don't quote me on that I just remember having some problems about 3 years ago with Qt! Hmm this is beginning to sound like a leak. Peter Childs Decibel! wrote: > > > > On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote: > >> Gregory Stark wrote: > >> >"novnov" <[EMAIL PROTECTED]> writes: > >> > > >> > > >> >>Is there any plan to add such a capability to postgres? > >> >> > >> > > >> >It's been talked about. I wouldn't be surprised to see it in 8.4 but > >> >nobody's > >> >said they'll be doing it yet and there are a lot of other more > exciting > >> >ideas > >> >too. > >> > > >> > > >> From a admin tool developers perspective the ability to reorder columns > >> without manually copying to a new table and all that is pretty exiting > >> :-) > > > > Patches welcome. :) > > > > BTW, this is much more likely to happen if we divorce presentation order > > from actual storage order, something that there is some desire to do > > because it would allow the engine to automagically store things in the > > optimal ordering from an alignment standpoint. > > -- > > Decibel!, aka Jim Nasby[EMAIL PROTECTED] > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > > > > > -- > View this message in context: > http://www.nabble.com/Reordering-columns%2C-will-this-ever-be-simple--tf4231761.html#a12129772 > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend >
Re: [GENERAL] Reordering columns, will this ever be simple?
I would like to make a request for this feature to be added to postgres. Postgres is a really great database. I'm still very much a novice at using postgres but in general, it's been a very good experience and I plan to use it as often as I can. The community is very helpful. My projects tend to be more seat of the pants than your average enterprise database. Adding the ability to reorder columns in a simple manner would be a very nice addition. I think such a feature would add to the 'curb appeal' of postgres...make it more user friendly, more flexible. I don't know anything about the internals of databases, how they actually work, but from my 'ignorance is bliss' perspective, it seems odd that one can alter field names at will in postgres but reordering columns is such a big deal. Maybe internally columns are id'd via index position, thus order matters, and the name is relatively light duty property? Maybe too hard to explain it in simple terms. Decibel! wrote: > > On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote: >> Gregory Stark wrote: >> >"novnov" <[EMAIL PROTECTED]> writes: >> > >> > >> >>Is there any plan to add such a capability to postgres? >> >> >> > >> >It's been talked about. I wouldn't be surprised to see it in 8.4 but >> >nobody's >> >said they'll be doing it yet and there are a lot of other more exciting >> >ideas >> >too. >> > >> > >> From a admin tool developers perspective the ability to reorder columns >> without manually copying to a new table and all that is pretty exiting >> :-) > > Patches welcome. :) > > BTW, this is much more likely to happen if we divorce presentation order > from actual storage order, something that there is some desire to do > because it would allow the engine to automagically store things in the > optimal ordering from an alignment standpoint. > -- > Decibel!, aka Jim Nasby[EMAIL PROTECTED] > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > -- View this message in context: http://www.nabble.com/Reordering-columns%2C-will-this-ever-be-simple--tf4231761.html#a12129772 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Erik Jones wrote: > > On Aug 9, 2007, at 1:14 PM, Greg Smith wrote: > >> On Thu, 9 Aug 2007, Erik Jones wrote: >> >>> Perhaps we could have a nice, friendly discussion on using surrogate >>> primary keys v. string based keys? Or, I think the body of the >>> "nulls are bad" dead horse is collecting flies if anyone wants to >>> take a swing at it... >> >> Following the handbook for dead horse riding[1], obviously technique >> #12 "Harnessing several dead horses together for increased speed" >> would allow faster progress were all these addressed at once. >> >> [1] http://soli.inav.net/~catalyst/Humor/dhorse.htm >> > > That's awesome. And very sad at the same time. Joshua D. Drkae > > Erik Jones > > Software Developer | Emma® > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGu13jATb/zqfZUUQRAgquAKCC6kw2tLdnxP5wzoQ5iZu+mElw2gCgoj8l CRPzhYI8FoBjWvNwN8LTsvs= =HuQm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Reordering columns, will this ever be simple?
On Aug 9, 2007, at 1:14 PM, Greg Smith wrote: On Thu, 9 Aug 2007, Erik Jones wrote: Perhaps we could have a nice, friendly discussion on using surrogate primary keys v. string based keys? Or, I think the body of the "nulls are bad" dead horse is collecting flies if anyone wants to take a swing at it... Following the handbook for dead horse riding[1], obviously technique #12 "Harnessing several dead horses together for increased speed" would allow faster progress were all these addressed at once. [1] http://soli.inav.net/~catalyst/Humor/dhorse.htm That's awesome. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
On Thu, 9 Aug 2007, Erik Jones wrote: Perhaps we could have a nice, friendly discussion on using surrogate primary keys v. string based keys? Or, I think the body of the "nulls are bad" dead horse is collecting flies if anyone wants to take a swing at it... Following the handbook for dead horse riding[1], obviously technique #12 "Harnessing several dead horses together for increased speed" would allow faster progress were all these addressed at once. [1] http://soli.inav.net/~catalyst/Humor/dhorse.htm -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Reordering columns, will this ever be simple?
On 8/9/07, Erik Jones <[EMAIL PROTECTED]> wrote: > > On Aug 8, 2007, at 7:14 PM, Martijn van Oosterhout wrote: > > > > But this is a thoroughly dead horse, lets not beat it up again. > > Hah! Perhaps we could have a nice, friendly discussion on using > surrogate primary keys v. string based keys? Or, I think the body of > the "nulls are bad" dead horse is collecting flies if anyone wants to > take a swing at it... I'll stop now. :-) Neighh! I can't believe you're trying to stirrup trouble here. I don't want to seem like a nag, but we don't need to saddle the mailing list with even more flame wars. People reading the list might think we've been sniffing glue to get our kicks! OK, I'm gonna go before I make a horse's arse of myself anymare. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns, will this ever be simple?
On Aug 8, 2007, at 7:14 PM, Martijn van Oosterhout wrote: But this is a thoroughly dead horse, lets not beat it up again. Hah! Perhaps we could have a nice, friendly discussion on using surrogate primary keys v. string based keys? Or, I think the body of the "nulls are bad" dead horse is collecting flies if anyone wants to take a swing at it... I'll stop now. :-) Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Reordering columns, will this ever be simple?
On Wed, Aug 08, 2007 at 11:37:11AM -0500, Scott Marlowe wrote: > If the danger of implementing this is some subtle bug that eats my > data, I'd just as soon do without. It's not a feature I've ever felt > the need for really. And there are lots of cool features I could > think of I'd want before this. What I'm more interested in the possibility of rearranging the physical order of columns at the CREATE TABLE stage to optimise access to various fields (move fixed width fields to front) and to minimise cost of padding of said fields. I imagine this could provide useful savings on wide tables and multicolumn indexes, but you have to decouple logical and physical ordering to do it. But this is a thoroughly dead horse, lets not beat it up again. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Reordering columns, will this ever be simple?
On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote: > Gregory Stark wrote: > >"novnov" <[EMAIL PROTECTED]> writes: > > > > > >>Is there any plan to add such a capability to postgres? > >> > > > >It's been talked about. I wouldn't be surprised to see it in 8.4 but > >nobody's > >said they'll be doing it yet and there are a lot of other more exciting > >ideas > >too. > > > > > From a admin tool developers perspective the ability to reorder columns > without manually copying to a new table and all that is pretty exiting :-) Patches welcome. :) BTW, this is much more likely to happen if we divorce presentation order from actual storage order, something that there is some desire to do because it would allow the engine to automagically store things in the optimal ordering from an alignment standpoint. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpEp5PM6K98S.pgp Description: PGP signature
Re: [GENERAL] Reordering columns, will this ever be simple?
On 8/8/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote: > > "novnov" <[EMAIL PROTECTED]> writes: > > > > > Is there any plan to add such a capability to postgres? > > > > It's been talked about. I wouldn't be surprised to see it in 8.4 but > > nobody's > > said they'll be doing it yet and there are a lot of other more exciting > > ideas > > too. > > Doubt it, patches to implement this have been submitted and rejected in > the past. I don't see any reason why 8.4 would be any different. If the danger of implementing this is some subtle bug that eats my data, I'd just as soon do without. It's not a feature I've ever felt the need for really. And there are lots of cool features I could think of I'd want before this. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Reordering columns, will this ever be simple?
On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote: > "novnov" <[EMAIL PROTECTED]> writes: > > > Is there any plan to add such a capability to postgres? > > It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's > said they'll be doing it yet and there are a lot of other more exciting ideas > too. Doubt it, patches to implement this have been submitted and rejected in the past. I don't see any reason why 8.4 would be any different. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Reordering columns, will this ever be simple?
Right, rel theory is fine, but I work with databases all day long and in the various lists that shows the fields in a table, placing them in some kind of order, other than chron by creation time, is very useful and efficient. It may not matter to the database engine but it matters to the human engine, in this case. Views don't help solve the basic issue. It's not a fatal issue, just one that would help streamline postgres, IMO. Josh Trutwin wrote: > > On Tue, 7 Aug 2007 11:07:39 -0700 (PDT) > novnov <[EMAIL PROTECTED]> wrote: > > > >> Is there any plan to add such a capability to postgres? Is there >> deep seated reason why reordering columns can't be handled without >> doing a root canal on the database? > > Probably because the theory behind the relational model (at least as > far as I understand it) is that column and row ordering is irrelevant. > > From http://en.wikipedia.org/wiki/Relational_model : > > "The relational model requires there to be no significance to any > ordering of the attributes of a relation." > > Just like if you do a SELECT * that the order of the rows returned > could change at any time unless you specify an ORDER BY clause. > > That said, most people I imagine like being able to "re-order" the > columns that a SELECT * produces. > >> If there already is some simple way to reorder columns, please let >> me know. > > CREATE VIEW my_favorite_order AS > SELECT col2, col1, col5, col4, etc > FROM base_table; > > SELECT * FROM my_favorite_order; > > Only drawback is that you have to re-build the view if the column > names in the base table change. > > Josh > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > > -- View this message in context: http://www.nabble.com/Reordering-columns%2C-will-this-ever-be-simple--tf4231761.html#a12044467 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Reordering columns, will this ever be simple?
Gregory Stark wrote: "novnov" <[EMAIL PROTECTED]> writes: Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. From a admin tool developers perspective the ability to reorder columns without manually copying to a new table and all that is pretty exiting :-) Tony Caduto AM Software Design http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reordering columns, will this ever be simple?
"novnov" <[EMAIL PROTECTED]> writes: > Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reordering columns, will this ever be simple?
On Tue, 7 Aug 2007 11:07:39 -0700 (PDT) novnov <[EMAIL PROTECTED]> wrote: > Is there any plan to add such a capability to postgres? Is there > deep seated reason why reordering columns can't be handled without > doing a root canal on the database? Probably because the theory behind the relational model (at least as far as I understand it) is that column and row ordering is irrelevant. From http://en.wikipedia.org/wiki/Relational_model : "The relational model requires there to be no significance to any ordering of the attributes of a relation." Just like if you do a SELECT * that the order of the rows returned could change at any time unless you specify an ORDER BY clause. That said, most people I imagine like being able to "re-order" the columns that a SELECT * produces. > If there already is some simple way to reorder columns, please let > me know. CREATE VIEW my_favorite_order AS SELECT col2, col1, col5, col4, etc FROM base_table; SELECT * FROM my_favorite_order; Only drawback is that you have to re-build the view if the column names in the base table change. Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Reordering columns, will this ever be simple?
One of the few problems I have with postgres is that one is stuck with columns ordered by time of creation, unless one resorts to dropping the table and rebuilding from ddl. If you have data and a bunch of contraints, it's not simple. It's not a critical capability either, but reordering columns in the other databases I work with is very simple. Schemas do evolve and I like to keep things in order, naturally. Is there any plan to add such a capability to postgres? Is there deep seated reason why reordering columns can't be handled without doing a root canal on the database? If there already is some simple way to reorder columns, please let me know. -- View this message in context: http://www.nabble.com/Reordering-columns%2C-will-this-ever-be-simple--tf4231761.html#a12039408 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
