CREATE TEXT INDEX

Creates a text index used to perform text searches.

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

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

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.

Do not alter the contents or definitions of the text index. If the contents or definitions of the text index are altered, then the results will not appropriately match the source table.

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.

Behavior

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

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