Обсуждение: Re-order columns?
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.
On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> 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.
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.
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
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" <david.g.johnston@gmail.com>:
On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> 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 longThere 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 -
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 <holger@jakobs.com> 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" <david.g.johnston@gmail.com>:On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> 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 longThere 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
wells.oliver@gmail.com
wells.oliver@gmail.com
On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> 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.
Hi,
On Wed, Jul 29, 2020 at 10:45 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> 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 longThere 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
On Wednesday, July 29, 2020, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> 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.
David,
On Wed, Jul 29, 2020 at 11:11 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, July 29, 2020, David G. Johnston <david.g.johnston@gmail.com> wrote:On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> 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...
Greetings, * Holger Jakobs (holger@jakobs.com) wrote: > Actually, the order of columns is of _no_ importance, since columns are identified by name and not by column number. It'sthe 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 rootedin 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