MIME-Version: 1.0
References: 
 <CAOC+FBUm=b0a2j0h=qDSCqVwO8yWm2fgYSwH2=StVsia4eYLMQ@mail.gmail.com>
 <CAKFQuwYQbDVoKMT=Eem92F0xitXNgM_iCDMtzjChyT3pAi9N5w@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwYQbDVoKMT=Eem92F0xitXNgM_iCDMtzjChyT3pAi9N5w@mail.gmail.com>
From: Olivier Gautherot <ogautherot@gautherot.net>
Date: Wed, 29 Jul 2020 23:10:29 +0200
Message-ID: 
 <CAJ7S9TUGYeDNECfv1MJ-92REXBTiO2siQnPujU1qE-uN1NOsaQ@mail.gmail.com>
Subject: Re: Re-order columns?
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Wells Oliver <wells.oliver@gmail.com>,
 pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000077dbb05ab9afd0f"
Precedence: bulk

--000000000000077dbb05ab9afd0f
Content-Type: text/plain; charset="UTF-8"

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 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

--000000000000077dbb05ab9afd0f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div><div dir=3D"ltr" class=3D"gmail_sign=
ature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"ltr"=
><div>Hi,</div></div></div></div></div></div><br><div class=3D"gmail_quote"=
><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Jul 29, 2020 at 10:45 PM Dav=
id G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.jo=
hnston@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">On Wednesday, July 29, 2020, Wells Oliver &lt;<a href=3D"ma=
ilto:wells.oliver@gmail.com" target=3D"_blank">wells.oliver@gmail.com</a>&g=
t; wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"=
ltr">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 co=
lumn at the end where you like it, then some guy comes along and is like &q=
uot;oh let&#39;s add X Y and Z&quot; and your OCD really really really want=
s the modified_at col at the end, and blowing away/re-populating the table =
takes way too long<div><div dir=3D"ltr"><div dir=3D"ltr"><div></div></div><=
/div></div></div></blockquote><div><br></div><div>There is not.=C2=A0 Every=
 so often this gets looked at but has yet to result in a commit.</div></blo=
ckquote><div><br></div><div>It is an issue that pops up regularly and there=
 are technical issues behind. It has been fixed in other engines but not ye=
t here.</div><div><br></div><div>If it is critical for your application, I =
could suggest 2 ways:</div><div><br></div><div>1) run a SELECT INTO taking =
care of the proper column order everytime you ALTER the table (it may or ma=
y not be doable depending on your specific case)</div><div><br></div><div>2=
) place a view in front of the table with the expected order</div><div><br>=
</div><div>The latter will surely reduce the maintenance, overhead, downtim=
e... Now if the order is really critical, you should probably specify the c=
olumns list when you access the=C2=A0table=C2=A0 (knowing it can be a pain =
if there are many). Note that if it gets solved, it will most probably be d=
one in the catalog and not the table itself.</div><div><br></div><div>Hope =
it helps</div><div>Olivier</div></div></div>

--000000000000077dbb05ab9afd0f--