Обсуждение: 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.

On Tue, 7 Aug 2007 11:07:39 -0700 (PDT)
novnov <novnovice@gmail.com> wrote:

<snip>

> 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

"novnov" <novnovice@gmail.com> 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

Gregory Stark wrote:
> "novnov" <novnovice@gmail.com> 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

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 <novnovice@gmail.com> wrote:
>
> <snip>
>
>> 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.

On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote:
> "novnov" <novnovice@gmail.com> 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   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On 8/8/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Tue, Aug 07, 2007 at 08:15:19PM +0100, Gregory Stark wrote:
> > "novnov" <novnovice@gmail.com> 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.

On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote:
> Gregory Stark wrote:
> >"novnov" <novnovice@gmail.com> 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                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

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   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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®
erik@myemma.com
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

On 8/9/07, Erik Jones <erik@myemma.com> 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.  :-)

Neighhhhhh!  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.

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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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®
erik@myemma.com
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

-----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®
> erik@myemma.com
> 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-----

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" <novnovice@gmail.com> 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                        decibel@decibel.org
> 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.