Re-order columns?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Re-order columns?

Wells Oliver-2
I know this kind of dumb, but is there a way to re-order columns? You know, you have your awesome table all defined, your favorite modified_at column at the end where you like it, then some guy comes along and is like "oh let's add X Y and Z" and your OCD really really really wants the modified_at col at the end, and blowing away/re-populating the table takes way too long.



--
Wells Oliver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

David G Johnston
On Wednesday, July 29, 2020, Wells Oliver <[hidden email]> wrote:
I know this kind of dumb, but is there a way to re-order columns? You know, you have your awesome table all defined, your favorite modified_at column at the end where you like it, then some guy comes along and is like "oh let's add X Y and Z" and your OCD really really really wants the modified_at col at the end, and blowing away/re-populating the table takes way too long

There is not.  Every so often this gets looked at but has yet to result in a commit.

David J. 

Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

Holger Jakobs-2
Actually, the order of columns is of _no_ importance, since columns are identified by name and not by column number. It's the same as the order of rows in a table - undefined (unless you use order by).

That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.

Of you prefer a different order, use a view.


Am 29. Juli 2020 22:45:06 MESZ schrieb "David G. Johnston" <[hidden email]>:
On Wednesday, July 29, 2020, Wells Oliver <[hidden email]> wrote:
I know this kind of dumb, but is there a way to re-order columns? You know, you have your awesome table all defined, your favorite modified_at column at the end where you like it, then some guy comes along and is like "oh let's add X Y and Z" and your OCD really really really wants the modified_at col at the end, and blowing away/re-populating the table takes way too long

There is not.  Every so often this gets looked at but has yet to result in a commit.

David J. 


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

Wells Oliver-2
The order of columns is important! I can't go to sleep at night knowing my modified_at isn't at the end of the table! This is serious business.

On Wed, Jul 29, 2020 at 1:50 PM Holger Jakobs <[hidden email]> wrote:
Actually, the order of columns is of _no_ importance, since columns are identified by name and not by column number. It's the same as the order of rows in a table - undefined (unless you use order by).

That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.

Of you prefer a different order, use a view.


Am 29. Juli 2020 22:45:06 MESZ schrieb "David G. Johnston" <[hidden email]>:
On Wednesday, July 29, 2020, Wells Oliver <[hidden email]> wrote:
I know this kind of dumb, but is there a way to re-order columns? You know, you have your awesome table all defined, your favorite modified_at column at the end where you like it, then some guy comes along and is like "oh let's add X Y and Z" and your OCD really really really wants the modified_at col at the end, and blowing away/re-populating the table takes way too long

There is not.  Every so often this gets looked at but has yet to result in a commit.

David J. 


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -


--
Wells Oliver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

David G Johnston
In reply to this post by Holger Jakobs-2
On Wednesday, July 29, 2020, Holger Jakobs <[hidden email]> wrote:

That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.

Technical reasons are real reasons.  The order of columns is very real, guaranteed (beware of inherits), thing in PostgreSQL whereas there is no guarantee regarding row order outside of a select query order by.  That said I do agree that fulfilling “ocd” drives isn’t a strong use case to work on the feature.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

Olivier Gautherot-2
In reply to this post by David G Johnston
Hi,

On Wed, Jul 29, 2020 at 10:45 PM David G. Johnston <[hidden email]> wrote:
On Wednesday, July 29, 2020, Wells Oliver <[hidden email]> wrote:
I know this kind of dumb, but is there a way to re-order columns? You know, you have your awesome table all defined, your favorite modified_at column at the end where you like it, then some guy comes along and is like "oh let's add X Y and Z" and your OCD really really really wants the modified_at col at the end, and blowing away/re-populating the table takes way too long

There is not.  Every so often this gets looked at but has yet to result in a commit.

It is an issue that pops up regularly and there are technical issues behind. It has been fixed in other engines but not yet here.

If it is critical for your application, I could suggest 2 ways:

1) run a SELECT INTO taking care of the proper column order everytime you ALTER the table (it may or may not be doable depending on your specific case)

2) place a view in front of the table with the expected order

The latter will surely reduce the maintenance, overhead, downtime... Now if the order is really critical, you should probably specify the columns list when you access the table  (knowing it can be a pain if there are many). Note that if it gets solved, it will most probably be done in the catalog and not the table itself.

Hope it helps
Olivier
Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

David G Johnston
In reply to this post by David G Johnston
On Wednesday, July 29, 2020, David G. Johnston <[hidden email]> wrote:
On Wednesday, July 29, 2020, Holger Jakobs <[hidden email]> wrote:

That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.

Technical reasons are real reasons.  The order of columns is very real, guaranteed (beware of inherits), thing in PostgreSQL whereas there is no guarantee regarding row order outside of a select query order by.  That said I do agree that fulfilling “ocd” drives isn’t a strong use case to work on the feature.

As for the theory, a tuple is an ordered list so the ordering of columns is a precise application of that definition and a relation is just a set (i.e., unordered) of tuples.  So the claim this isn’t rooted in relational theory just seems wrong.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

Olivier Gautherot-2
David,

On Wed, Jul 29, 2020 at 11:11 PM David G. Johnston <[hidden email]> wrote:
On Wednesday, July 29, 2020, David G. Johnston <[hidden email]> wrote:
On Wednesday, July 29, 2020, Holger Jakobs <[hidden email]> wrote:

That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.

Technical reasons are real reasons.  The order of columns is very real, guaranteed (beware of inherits), thing in PostgreSQL whereas there is no guarantee regarding row order outside of a select query order by.  That said I do agree that fulfilling “ocd” drives isn’t a strong use case to work on the feature.

As for the theory, a tuple is an ordered list so the ordering of columns is a precise application of that definition and a relation is just a set (i.e., unordered) of tuples.  So the claim this isn’t rooted in relational theory just seems wrong.

The issue here is to ensure that a given column always shows up at the end, even after you added other columns. In other words, it is a kind of "ALTER TABLE ... INSERT COLUMN AT ..." instead of "... ADD COLUMN ...". It is more about data modeling than relational theory...

Reply | Threaded
Open this post in threaded view
|

Re: Re-order columns?

Stephen Frost
In reply to this post by Holger Jakobs-2
Greetings,

* Holger Jakobs ([hidden email]) wrote:
> Actually, the order of columns is of _no_ importance, since columns are identified by name and not by column number. It's the same as the order of rows in a table - undefined (unless you use order by).
>
> That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.

There's very practical reasons to think about the ordering of columns,
at least in PostgreSQL- you can end up creating holes in your row if you
order the columns in the wrong way and that can lead to wasted space.

There was a patch floating around to allow re-ordering, which would also
then allow us to re-order the row underneath to avoid wasting space (as
much as possible, anyway) which would be really rather nice, but sadly
it didn't get enough push on it to make it happen.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment