Re: Re-order columns?

Hi,

On Wed, Jul 29, 2020 at 10:45 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wednesday, July 29, 2020, Wells Oliver <wells(dot)oliver(at)gmail(dot)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.
>

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