Search code examples
monetdb

how to add a column after another one in monetDB

I am trying to add a new column in a monetDB database and I want it positioned after a specific one. In mysql this is possible using the AFTER keyword.

ALTER TABLE myTable ADD myNewColumn VARCHAR(255) AFTER myOtherColumn

I am trying this in the mclient:

sql>ALTER TABLE dbname.table_name ADD COLUMN new_name AFTER existing_name SET DEFAULT NULL;

What I get is a syntax error:

syntax error, unexpected AFTER in: "ALTER TABLE dbname.table_name ADD COLUMN new_name AFTER"

It is true that the ALTER documentation does not specify that AFTER exists, but I am hoping that anybody knows an alternative.

Solution

  • The safe way is to create an new table with the columns properly ordered and move the data; you probably know this already. However if you really cannot do that, create a view:

    CREATE VIEW AS SELECT [order the columns however you want here] FROM your_table;