Exception while altering table (Crate@3.2.2)

CrateDB
1

Suppose we have a table;

cr> create table documents_a (
...   title text,
...   body text,
...   INDEX title_body_ft
...     using fulltext(title, body) with (analyzer = 'english')
... );

Now we need to alter and add a column in it such that it contains all the other field values

alter table documents_a ADD COLUMN new_column string index using fulltext("body","title","new_column") with (analyzer = 'english');

This gives following exception :

SQLActionException[SQLParseException: line 1:74: extraneous input ‘(’ expecting {, ‘NOT’, ‘PRIMARY KEY’, ‘INDEX’, ‘STORAGE’, ‘;’}]

How to resolve this exception and where am I doing it wrong. Also, I want to have values of all the columns in the newly added column.
Crate Version : 3.2.2
JDK : 1.8

2

CrateDB doesn’t support adding an index column using ALTER TABLE (after table creation), it must be defined on CREATE TABLE.
If it is required to do it later on, a workaround could be to create a new table, copy data over via insert-from-subquery, drop the old table, rename the new one.

3

Thanks for the workaround Sebastian @smu
But what if data is >10gb, copying it again and again, would be so time-consuming? :fearful: