CREATE TEXT INDEX

Creates a text index used to perform text searches.

Creates a text index used to perform text searches. If data within a table is partitioned, then an extra column appears in the text index, showing the partition.

Syntax

CREATE TEXT INDEX [[database.]schema.]txtindex-name
 ON [schema.]source-table (unique-id, text-field [, column-name,...])
 [STEMMER {stemmer-name(stemmer-input-data-type)| NONE}]
 [TOKENIZER tokenizer-name(tokenizer-input-data-type)];

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

If you do not specify a schema, the table is created in the default schema.

txtindex-name
The text index name.
source-table
The source table to index.
unique-id
The name of the column in the source table that contains a unique identifier. Any data type is permissible. The column must be the primary key in the source table.
text-field
The name of the column in the source table that contains the text field. Valid data types are:
  • CHAR

  • VARCHAR

  • LONG VARCHAR

  • VARBINARY

  • LONG VARBINARY

Nulls are allowed.

column-name
The name of a column or columns to be included as additional columns.
stemmer-name
The name of the stemmer.
stemmer-input-data-type
The input data type of the stemmer-name function.
tokenizer-name
Specifies the name of the tokenizer.
tokenizer-input-data-type
This value is the input data type of the tokenizer-name function. It can accept any number of arguments.

If a Vertica tokenizers is used, then this parameter can be omitted.

Privileges

The index automatically inherits the query permissions of its parent table. The table owner and dbadmin will be allowed to create and/or modify the indices.

Requirements

  • 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.

Examples

The following example shows how to create a text index with an additional unindexed column on the table t_log using the CREATE TEXT INDEX statement:

=> CREATE TEXT INDEX t_log_index ON t_log (id, text, day_of_week);
CREATE INDEX
=> SELECT * FROM t_log_index;
        token          | doc_id | day_of_week
-----------------------+--------+-------------
'catalog               |      1 | Monday
'dbadmin'              |      2 | Monday
2014-06-04             |      1 | Monday
2014-06-04             |      2 | Monday
2014-06-04             |      3 | Monday
2014-06-04             |      4 | Monday
2014-06-04             |      5 | Monday
2014-06-04             |      6 | Monday
2014-06-04             |      7 | Monday
2014-06-04             |      8 | Monday
45035996273704966      |      3 | Tuesday
45035996273704968      |      4 | Tuesday
<INFO>                 |      1 | Tuesday
<INFO>                 |      6 | Tuesday
<INFO>                 |      7 | Tuesday
<INFO>                 |      8 | Tuesday
<WARNING>              |      2 | Tuesday
<WARNING>              |      3 | Tuesday
<WARNING>              |      4 | Tuesday
<WARNING>              |      5 | Tuesday

...

(97 rows)

The following example shows a text index, tpart_index, created from a partitioned source table:

=> SELECT * FROM tpart_index;
         token          | doc_id | partition
------------------------+--------+-----------
 0                      |      4 |      2014
 0                      |      5 |      2014
 11:00:49.568           |      4 |      2014
 11:00:49.568           |      5 |      2014
 11:00:49.569           |      6 |      2014
 <INFO>                 |      6 |      2014
 <WARNING>              |      4 |      2014
 <WARNING>              |      5 |      2014
 Database               |      6 |      2014
 Execute:               |      6 |      2014
 Object                 |      4 |      2014
 Object                 |      5 |      2014
 [Catalog]              |      4 |      2014
 [Catalog]              |      5 |      2014
 'catalog               |      1 |      2013
 'dbadmin'              |      2 |      2013
 0                      |      3 |      2013
 11:00:49.568           |      1 |      2013
 11:00:49.568           |      2 |      2013
 11:00:49.568           |      3 |      2013
 11:00:49.570           |      7 |      2013
 11:00:49.571           |      8 |      2013
 45035996273704966      |      3 |      2013

...

(89 rows)

See also