How to create text index on a flex table which has composite primary key

rajatpaliwal86

dbadmin=> CREATE TEXT INDEX plain_text_search_index ON f_network_events(id, raw) TOKENIZER public.FlexTokenizer(long varbinary);
ROLLBACK 4550: Referenced primary key constraint does not exist

id is simply the identity column. The primary key is the composite of multiple columns but I don't know how to refer them in text index.

Jim_Knicely

The requirements for a text index are:

• Requires there be a column with a unique identifier set as the primary key.
• The source table must have an associated projection, and must be both sorted and segmented by the primary key.

See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEXTINDEX.htm

rajatpaliwal86

@Jim_Knicely said:
The requirements for a text index are:

• Requires there be a column with a unique identifier set as the primary key.
• The source table must have an associated projection, and must be both sorted and segmented by the primary key.

See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEXTINDEX.htm

Yes, but unfortunately the primary key of the flex table is composed of multiple keys.
create flex table if not exists athena_test.f_network_events
(
"id" IDENTITY(1,1),
"sensor_id" varchar(100) NOT NULL,
"component_id" int NOT NULL,
"flow_id" bigint NOT NULL,
..
..
CONSTRAINT pk PRIMARY KEY ("sensor_id", "component_id", "flow_id") ENABLED
);

How can I create text index on this flex table? Any help would be appreciated.

Bryan_H

Did you try the procedure to create a text index on flex table described at https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Tables/TextSearch/CreatingATextIndexOnAFlexTable.htm

rajatpaliwal86

@Bryan_H said:
Did you try the procedure to create a text index on flex table described at https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Tables/TextSearch/CreatingATextIndexOnAFlexTable.htm

Yes, but the issue is that it restricts the primary key of a single column only, however my flex table has a primary key composited of multiple columns.