Can yugabyte-db modify column ordinal position?

[bnuzhouwei]

MySQL and TiDB offer alter column ... after ... to modify column ordinal position.

MSSQL offer a gui tool to modify column position by recreate table automatically.

Can yugabyte-db do this by some method?

[bnuboy]

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

[ddorian]

Hi @equiclouds

Currently each column is stored as a separate key-value in rocksdb underneath and not the full-row-inside-tuple like in normal PostgreSQL.

So this operation doesn't exist in our case. We first need to implement https://github.com/yugabyte/yugabyte-db/issues/3520. Closing as non applicable.

[bnuzhouwei]

Currently each column is stored as a separate key-value in rocksdb underneath and not the full-row-inside-tuple like in normal PostgreSQL.

So this operation doesn't exist in our case. We first need to implement #3520. Closing as non applicable.

Yeah, if stored as a [separate key-value in rocksdb underneath], may i update the metadata to modify columns positions, such that i will show changes in the table desinger (such as navicat).

[ddorian]

Yeah, if stored as a [separate key-value in rocksdb underneath], may i update the metadata to modify columns positions, such that i will show changes in the table desinger (such as navicat).

By doing what operation ? Cause an "alter column position" isn't available in normal PostgreSQL, hence not with us.

[bnuzhouwei]

If i update attnum in pg_attribute

update pg_attribue set attnum=@attnum where oid=object_id(@relname)

will cause shutdown from postgres.

For YGDB store as kvs, is this operation safe?

[ddorian]

For YGDB store as kvs, is this operation safe?

I'll get a developer from YSQL team to comment here.

[bnuzhouwei]

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.

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

Or just updating the metabase:

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

[ddorian]

@equiclouds column names (IDs, really) become part of the DocDB key in rocksdb. It’d require rewriting the entire table to change column position.

[bnuzhouwei]

Is there a logic position that dba can set without set the phisical postion, so that the column postions saw in db desginer software are same as expected.

[ddorian]

@equiclouds by setting the column positions order right from the start. Or by dropping/re-creating the tables. Currently we take column-id into the key-value in rocksdb so even if we added this features it would be a table-rewrite.