Offer a interface to reset column ordinal position by modifying metadata

[bnuzhouwei]

We do have the internal metadata concepts to make this happen in a relatively inexpensive way. That being said, we have not implemented this functionality. Please file a github issue to track it and discuss various design point. I will warn you that I don’t think this is something we’re in any hurry to implement. That issue can also be a way to gauge interest and work out syntax details. (by ajwerner, Andrew Werner, Roacher)

The process of designing software is gradual. In the DB design process, we can’t do a perfect solution that considerded all the properties for implement the business. Some columns may be added, if new columns can only at the end of columns list. The automancally generated db document will be inaesthetic and unnatural, that will cost must time to reposion them in Words or Excel.

Describe the solution you'd like

MSSQL also provide a GUI tool for reposition the columns by automancally recreate the table. I think for CRDB is a NewSQL product with kv store, it is much easier to reposition columns without recreated table needed. May be just modify some metadata by (just like TiDB another NewSQL db):

alter table alter column columname after columnother

Describe alternatives you've considered

Or just updating the metabase:

update meta set attnum=@attnum where relname=@relname and attname=@attname

Jira issue: CRDB-7891

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @nvanbenschoten (found keywords: kv)
• @ajwerner (found keywords: alter table)
• @jordanlewis (found keywords: alter table)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[bnuboy]

It is common to update the structure of the tables, thus it is very helpful for developers to modify the column ordinal position.

[manavchawla98]

Hi! I'd like to help in solving this issue. Could you please give me some guidance on what part of the code to look at with some example use cases?

[alimoosavi]

@bnuzhouwei I am new to cockroachdb project and this issue seems attractive to me , please assign this issue to me.