Skip to main content
`Ctrl`+`K`

# 7.3.63. `table_create`

## 7.3.63.1. Summary

`table_create` creates a new table in the current database. You need to create one or more tables to store and search data.

See Tables for table details.

## 7.3.63.2. Syntax

This command takes many parameters.

The required parameter is only `name` and otehrs are optional:

    table_create name
                 [flags=TABLE_HASH_KEY]
                 [key_type=null]
                 [value_type=null]
                 [default_tokenizer=null]
                 [normalizer=null]
                 [token_filters=null]
                 [path=null]

## 7.3.63.3. Usage

This section describes about the followings:

> - Create data store table

> - Create large data store table

> - Create lexicon

> - Create tag index table

> - Create range index table

### 7.3.63.3.1. Create data store table

You can use all table types for data store table. See Tables for all table types.

Table type is specified as `TABLE_${TYPE}` to `flags` parameter.

Here is an example to create `TABLE_NO_KEY` table:

Execution example:

    table_create Logs TABLE_NO_KEY
    # [[0,1337566253.89858,0.000355720520019531],true]

The `table_create` command creates a table that is named `Logs` and is `TABLE_NO_KEY` type.

If your records aren’t searched by key, `TABLE_NO_KEY` type table is suitable. Because `TABLE_NO_KEY` doesn’t support key but it is fast and small table. Storing logs into Groonga database is the case.

If your records are searched by key or referenced by one or more columns, `TABLE_NO_KEY` type isn’t suitable. Lexicon for fulltext search is the case.

### 7.3.63.3.2. Create large data store table

If you want to store many large keys, your table may not be able to store them. If total key data is larger than 4GiB, you can’t store all key data into your table by default.

Added in version 15.1.5: `TABLE_PAT_KEY` also supports `KEY_LARGE` flag.

You can expand the maximum total key size to 1TiB from 4GiB by `KEY_LARGE` flag. `KEY_LARGE` flag can be used with `TABLE_HASH_KEY` or `TABLE_PAT_KEY`. You can’t use `KEY_LARGE` flag with `TABLE_NO_KEY` nor `TABLE_DAT_KEY`.

Here is an example to create a table that can store many large keys:

Execution example:

    table_create Paths TABLE_HASH_KEY|KEY_LARGE ShortText
    # [[0,1337566253.89858,0.000355720520019531],true]

The `table_create` command creates a table that is named `Paths` and is `TABLE_HASH_KEY` type. The `Paths` table can store many large keys.

The above example is `TABLE_HASH_KEY` case even if a table is `TABLE_PAT_KEY`, you can set `KEY_LARGE` flag in the same way.

### 7.3.63.3.3. Create lexicon

You can use all table types except `TABLE_NO_KEY` for lexicon. Lexicon needs key support but `TABLE_NO_KEY` doesn’t support key.

Here is an example to create `TABLE_PAT_KEY` table:

Execution example:

    table_create Lexicon TABLE_PAT_KEY ShortText --default_tokenizer TokenBigram --normalizer NormalizerAuto
    # [[0,1337566253.89858,0.000355720520019531],true]

The `table_create` command creates the following table:

- The table is named `Lexicon`.

- The table is `TABLE_PAT_KEY` type table.

- The table’s key is `ShortText` type.

- The table uses `TokenBigram` tokenizer to extract tokens from a normalized text.

- The table uses `NormalizerAuto` normalizer to normalize a text.

`TABLE_PAT_KEY` is suitable table type for lexicon. Lexicon is used for fulltext search.

In fulltext search, predictive search may be used for fuzzy search. Predictive search is supported by `TABLE_PAT_KEY` and `TABLE_DAT_KEY`.

Lexicon has many keys because a fulltext target text has many tokens. Table that has many keys should consider table size because large table requires large memory. Requiring large memory causes disk I/O. It blocks fast search. So table size is important for a table that has many keys. `TABLE_PAT_KEY` is less table size than `TABLE_DAT_KEY`.

Because of the above reasons, `TABLE_PAT_KEY` is suitable table type for lexicon.

### 7.3.63.3.4. Create tag index table

You can use all table types except `TABLE_NO_KEY` for tag index table. Tag index table needs key support but `TABLE_NO_KEY` doesn’t support key.

Here is an example to create `TABLE_HASH_KEY` table:

Execution example:

    table_create Tags TABLE_HASH_KEY ShortText
    # [[0,1337566253.89858,0.000355720520019531],true]

The `table_create` command creates a table that is named `Tags`, is `TABLE_HASH_KEY` type and has `ShortText` type key.

`TABLE_HASH_KEY` or `TABLE_DAT_KEY` are suitable table types for tag index table.

If you need only exact match tag search feature, `TABLE_HASH_KEY` is suitable. It is the common case.

If you also need predictive tag search feature (for example, searching `"groonga"` by `"gr"` keyword.), `TABLE_DAT_KEY` is suitable. `TABLE_DAT_KEY` is large table size but it is not important because the number of tags will not be large.

### 7.3.63.3.5. Create range index table

You can use `TABLE_PAT_KEY` and `TABLE_DAT_KEY` table types for range index table. Range index table needs range search support but `TABLE_NO_KEY` and `TABLE_HASH_KEY` don’t support it.

Here is an example to create `TABLE_DAT_KEY` table:

Execution example:

    table_create Ages TABLE_DAT_KEY UInt32
    # [[0,1337566253.89858,0.000355720520019531],true]

The `table_create` command creates a table that is named `Ages`, is `TABLE_DAT_KEY` type and has `UInt32` type key.

`TABLE_PAT_KEY` and `TABLE_DAT_KEY` are suitable table types for range index table.

If you don’t have many indexed items, `TABLE_DAT_KEY` is suitable. Index for age is the case in the above example. Index for age will have only 0-100 items because human doesn’t live so long.

If you have many indexed items, `TABLE_PAT_KEY` is suitable. Because `TABLE_PAT_KEY` is smaller than `TABLE_DAT_KEY`.

## 7.3.63.4. Parameters

This section describes all parameters.

### 7.3.63.4.1. Required parameters

There is only one required parameter.

#### 7.3.63.4.1.1. `name`

Specifies a table name to be created. `name` must be specified.

Here are available characters:

- `0` .. `9` (digit)

- `a` .. `z` (alphabet, lower case)

- `A` .. `Z` (alphabet, upper case)

- `#` (hash)

- `@` (at mark)

- `-` (hyphen)

- `_` (underscore) (NOTE: Underscore can’t be used as the first character.)

You need to create a name with one or more the above characters. Note that you can’t use `_` as the first character such as `_name`.

### 7.3.63.4.2. Optional parameters

There are some optional parameters.

#### 7.3.63.4.2.1. `flags`

Specifies a table type and table customize options.

Here are available flags:

Flag | Description
--- | ---
`TABLE_NO_KEY` | Array table. See also TABLE_NO_KEY.
`TABLE_HASH_KEY` | Hash table. See also TABLE_HASH_KEY.
`TABLE_PAT_KEY` | Patricia trie. See also TABLE_PAT_KEY.
`TABLE_DAT_KEY` | Double array trie. See also TABLE_DAT_KEY.
`KEY_WITH_SIS` | Enable Semi Infinite String. Require `TABLE_PAT_KEY`.
`KEY_LARGE` | Expand the maximum total key size to 1TiB from 4GiB. Require `TABLE_HASH_KEY` or `TABLE_PAT_KEY`.

Note

Since Groonga 2.1.0 `KEY_NORMALIZE` flag is deprecated. Use `normalizer` option with `NormalizerAuto` instead.

You must specify one of `TABLE_${TYPE}` flags. You can’t specify two or more `TABLE_${TYPE}` flags. For example, `TABLE_NO_KEY|TABLE_HASH_KEY` is invalid.

You can combine flags with `|` (vertical bar) such as `TABLE_PAT_KEY|KEY_WITH_SIS`.

See Tables for difference between table types.

The default flags are `TABLE_HASH_KEY`.

#### 7.3.63.4.2.2. `key_type`

Specifies key type.

If you specify `TABLE_HASH_KEY`, `TABLE_PAT_KEY` or `TABLE_DAT_KEY` as `flags` parameter, you need to specify `key_type` option.

See Data types for all types.

The default value is none.

#### 7.3.63.4.2.3. `value_type`

Specifies value type.

You can use value when you specify `TABLE_NO_KEY`, `TABLE_HASH_KEY` or `TABLE_PAT_KEY` as `flags` parameter. Value type must be a fixed size type. For example, `UInt32` can be used but `ShortText` cannot be used. Use columns instead of value.

The default value is none.

#### 7.3.63.4.2.4. `default_tokenizer`

Specifies the default tokenizer that is used on searching and data loading.

You must specify `default_tokenizer` for a table that is used for lexicon of fulltext search index. See Tokenizers for available tokenizers. You must choose a tokenizer from the list for fulltext search.

You don’t need to specify `default_tokenizer` in the following cases:

> - You don’t use the table as a lexicon.

> - You use the table as a lexicon but you don’t need fulltext search. For example:

>   > - Index target data isn’t text data such as `Int32` and `Time`.

>   > - You just need exact match search, prefix search and so on.

You can’t use `default_tokenizer` with `TABLE_NO_KEY` flag because a table that uses `TABLE_NO_KEY` flag can’t be used as lexicon.

You must specify `TABLE_HASH_KEY`, `TABLE_PAT_KEY`, `TABLE_DAT_KEY` to flags when you want to use the table as a lexicon.

The default value is none.

#### 7.3.63.4.2.5. `normalizer`

Specifies a normalizer that is used to normalize key.

You cannot use `normalizer` with `TABLE_NO_KEY` because `TABLE_NO_KEY` doesn’t support key.

See Normalizers for all normalizers.

The default value is none.

#### 7.3.63.4.2.6. `token_filters`

Specifies token filters separated by `,`. Token filters are used to process tokens.

You cannot use `token_filters` with `TABLE_NO_KEY` because `TABLE_NO_KEY` doesn’t support key.

See Token filters for all token filters.

The default value is none.

#### 7.3.63.4.2.7. `path`

Added in version 10.0.7.

Specifies a path for storing a table.

This option is useful if you want to store a table that you often use to fast storage (e.g. SSD) and store it that you don’t often use to slow storage (e.g. HDD).

You can use a relative path or an absolute path in this option. If you specify a relative path, it is resolved from the current directory for the `groonga` process.

The default value is none.

## 7.3.63.5. Return value

`table_create` returns `true` as body on success such as:

    [HEADER, true]

If `table_create` fails, error details are in `HEADER`.

See Output format for `HEADER`.

## 7.3.63.6. See also

- Tables

- column_create

- Tokenizers

- Normalizers

- Output format