query_default_field index parameter

Set the query_default_field index parameter to a column name to override the default field for basic text search queries from the contents field. Set the query_default_field index parameter to * to separately index each column in a composite index.

You do not need to specify the default field in a basic text search query. If you have a structured index on JSON or XML data, you can change the default field to one of the indexed field names or tags.

You do not need to specify the default field in a basic text search query. If you have a structured index on XML data, you can change the default field to one of the indexed tags.

You can create a composite bts index on multiple text columns. By default, columns are concatenated and indexed as a single string in the contents field. Regardless of which column name you specify in the query, the matching text from all the indexed columns is returned. You can use the query_default_field="*" index parameter to index each column separately so that you can query by column name, which becomes the index field name. When you use the query_default_field="*" index parameter, only the matching text from the column name that you specify in the query is returned. You query multiple columns by including their field names in the format fieldname:string.

You cannot create a composite index on JSON or BSON columns.

If you combine the query_default_field="*" index parameter with the xmltags index parameter, the composite index is created on only the XML columns.

Examples: Create composite indexes

The following examples use a table with the following structure:

CREATE TABLE address(
    fname      char(32),
    lname      char(32),
    address1   varchar(64),
    address2   varchar(64),
    city       char(32),
    province   char(32),
    country    char(32),
    postalcode char(10)
);

You can create a composite bts index on multiple columns in the address table by using the following statement, which matches each column data type with its corresponding operator class:

CREATE INDEX  bts_idx ON address(
    fname      bts_char_ops,
    lname      bts_char_ops,
    address1   bts_varchar_ops,
    address2   bts_varchar_ops,
    city       bts_char_ops,
    province   bts_char_ops,
    country    bts_char_ops,
    postalcode bts_char_ops) USING bts;

The resulting composite index concatenates all the columns into the contents field. The following two queries would produce the same results because the text is not indexed by column name:

SELECT * FROM address WHERE bts_contains(fname, 'john');
SELECT * FROM address WHERE bts_contains(address1, 'john');

Alternatively, you can create a composite bts index and specify that each column is indexed separately by including the query_default_field="*" index parameter:

CREATE INDEX  bts_idx ON address(
    fname      bts_char_ops,
    lname      bts_char_ops,
    address1   bts_varchar_ops,
    address2   bts_varchar_ops,
    city       bts_char_ops,
    province   bts_char_ops,
    country    bts_char_ops,
    postalcode bts_char_ops) USING bts (query_default_field="*");

The resulting composite index includes the column name with the indexed text. The following two queries would produce different results:

SELECT * FROM address WHERE bts_contains(fname, 'john');
SELECT * FROM address WHERE bts_contains(address1, 'john');

The first query finds matches for john in the fname column and the second query finds matches for john in the address1 column.

The following example searches for a row that contains specific text in two of its columns:

SELECT * FROM address WHERE bts_contains(fname, 'john AND city:nipigon');

This query returns the rows that contain both john in the fname column and nipigon in the city column.