Creating a Text Index

In the following example, you perform a text search using a source table called t_log. This source table has two columns:

You must associate a projection with the source table. Use a projection that is sorted by the primary key and either segmented by hash(id) or unsegmented. You can define this projection on the source table, along with any other existing projections.

Create a text index on the table for which you want to perform a text search.

=> CREATE TEXT INDEX text_index ON t_log (id, text);

The text index contains two columns:

If your table is partitioned then your text index also contains a third column named partition.

=> SELECT * FROM text_index;
          token         | doc_id | partition
------------------------+--------+-----------
<info>                  |      6 |      2014
<warning>               |      2 |      2014
<warning>               |      3 |      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

You create a text index on a source table only once. In the future, you do not have to re-create the text index each time the source table is updated or changed.

Your text index stays synchronized to the contents of the source table through any operation that is run on the source table. These operations include, but are not limited to: