Reset column ordinal position

In traditional relational databases, such as postgres, mssql, mysql, etc. It’s hard for them to reset column ordinal position. But for newsql databases, such as CRDB, is it possible to reset columns’ ordinal position easily for their use kv store. Just use:

alter table alter column columname after columnother

Hi @bnuzhouwei,

This is interesting. What is the motivation for changing the relative ordinal position of two columns in a table? Is this meant to improve performance? Are there other systems that support this?

Yes, TiDB, another NewSQL database support the DDL that change ordinal position.

For business is complicate. It it hard to include all columns in designing the database.
In the developing process, columns may need to add to the table with specified postion.

I’ve done a bit of reading into this topic and my take is that TiDB supports changing the ordinal position of columns because this is something MySQL supports, and TiDB implements the MySQL dialect of SQL. CockroachDB implements the PostgreSQL dialect of SQL, which does not support altering column positions within a table. So for now, I think the workarounds provided in that link are your best bet.

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?

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.


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.