I searched hard but could not find this information: Is it possible to set a fulltext index on subfields of an object field?
I mean like so: fulltext index on data_obj[‘message’]
CREATE TABLE fulltext_test3 (
ts TIMESTAMP WITHOUT TIME ZONE,
data_obj OBJECT(DYNAMIC) AS (
message TEXT INDEX using fulltext with (analyzer = 'english')
Ahh, ok perfect, will try …
Can full text index be added after the table is created on column message in above case? What is the syntax to add it using alter table command? Could someone please help on that?
Adding indexes or generated columns is currently not possible on tables that already hold data. You would need to recreate the table and use
INSERT INTO SELECT or
ok Thanks. Is there any way to drop the full text index created on top of all columns? I tried alter table mytable remove all_col_ft etc but no luck.
could you maybe share you schema. I am not 100% sure what you are trying to achieve.
It is not possible to remove columns / indexes from an existing table as of now. This is due how CrateDB stores and indexes data with Lucene. Any removal of columns/indexes would lead to a reindex of the table (Lucene segments) or not truly delete the data, but only adjust the schema.
Here is my schema.
CREATE TABLE mytable (
workday TIMESTAMP WITH TIME ZONE,
moreinfo OBJECT(DYNAMIC) AS (
PRIMARY KEY (workday, id),
INDEX all_col_ft USING FULLTEXT (col2, col4, col5, col6, moreinfo[‘col10’], moreinfo[‘col11’])
I want to drop all_col_ft index as it is eating up my disk space. Need SQL command to drop it.