Alter the data type of a column in MonetDB

1
votes

How can I alter the type of a column in an existing table in MonetDB? According to the documentation the code should be something like

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET ...

but then I am basically lost because I do not know which standard the SQL used by MonetDB follows here and I get a syntax error. If this statement is not possible I would be grateful for a workaround that is not too slow for large (order of 10^9 records) tables.

Note: I ran into this problem while doing some bulk data imports from csv files into a table in my database. One of the columns is of type INT but the values in the file at some point exceed the INT limit of 2^31-1 (yes, the table is big) and so the transaction aborts. After I found out the reason for this failure, I wanted to change it to BIGINT but all versions of SQL code I tried failed.

1
sqlmonetdb

1 Answers

3
votes

This is currently not supported. However, there is a workaround:

Example table for this example, say we want to change the type of column b from integer to double.

create table a(b integer);
insert into a values(42);
  1. Create a temporary column alter table a add column b2 double;
  2. Set data in temporary column to original data update a set b2=b;
  3. Remove the original column alter table a drop column b;
  4. Re-create the original column with the new type alter table a add column b double;
  5. Move data from temporary column to new column update a set b=b2;
  6. Drop the temporary column alter table a drop column b2;
  7. Profit

Note that this will change the ordering of columns if there are more than one. However, this is only a cosmetic issue.