WITH (storage parameter)

The WITH (storage parameter) sets a storage parameter on a table.

Syntax

create_index_with_storage_param::=

create_table_with_storage_param::=

Command parameters

Parameter	Description
`table`	The table to which you are setting the parameter.
`index`	The index to which you are setting the parameter.
`parameter_name`	The name of the storage parameter. See Storage parameters for a list of available parameters.

Storage parameters

Index parameters

Parameter name	Description	Data type	Default value
`bucket_count`	The number of buckets into which a will split.	Integer	The value of the `sql.defaults.default_hash_sharded_index_bucket_count`.
`geometry_max_x`	The maximum X-value of the for the object(s) being covered. This only needs to be set if you are using a custom .		Derived from SRID bounds, else `(1 << 31) -1`.
`geometry_max_y`	The maximum Y-value of the for the object(s) being covered. This only needs to be set if you are using a custom .		Derived from SRID bounds, else `(1 << 31) -1`.
`geometry_min_x`	The minimum X-value of the for the object(s) being covered. This only needs to be set if the default bounds of the SRID are too large/small for the given data, or SRID = 0 and you wish to use a smaller range (unfortunately this is currently not exposed, but is viewable on https://epsg.io/3857). By default, SRID = 0 assumes `[-min int32, max int32]` ranges.		Derived from SRID bounds, else `-(1 << 31)`.
`geometry_min_y`	The minimum Y-value of the for the object(s) being covered. This only needs to be set if you are using a custom .		Derived from SRID bounds, else `-(1 << 31)`.
`s2_level_mod`	`s2_max_level` must be divisible by `s2_level_mod`. `s2_level_mod` must be between `1` and `3`.	Integer	`1`
`s2_max_cells`	The maximum number of S2 cells used in the covering. Provides a limit on how much work is done exploring the possible coverings. Allowed values: `1-30`. You may want to use higher values for odd-shaped regions such as skinny rectangles. Used in .	Integer	`4`
`s2_max_level`	The maximum level of S2 cell used in the covering. Allowed values: `1-30`. Setting it to less than the default means that CockroachDB will be forced to generate coverings using larger cells. Used in .	Integer	`30`

The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:

fillfactor

Table parameters

Parameter name	Description	Data type	Default value
`exclude_data_from_backup`	Exclude the data in this table from any future backups.	Boolean	`false`
`infer_rbr_region_col_using_constraint`	For tables, automatically populate the hidden `crdb_region` column on `INSERT`, `UPDATE`, and `UPSERT` by looking up the region of the referenced parent row. Set this parameter to the name of a constraint on the table that includes the `crdb_region` column. The foreign key cannot be dropped while the parameter is set.	String	`NULL`
`schema_locked`	Indicates that a is not currently ongoing on this table. CockroachDB automatically unsets this parameter before performing a schema change and reapplies it when done. Enabling `schema_locked` can help running on this table.	Boolean	`false`
`sql_stats_automatic_collection_enabled`	Enable automatic collection of and statistics for this table.	Boolean	`true`
`sql_stats_automatic_collection_min_stale_rows`	Minimum number of stale rows in this table that will trigger a full statistics refresh.	Integer	500
`sql_stats_automatic_collection_fraction_stale_rows`	Fraction of stale rows in this table that will trigger a full statistics refresh.	Float	0.2
`sql_stats_automatic_full_collection_enabled`	Enable automatic collection of for this table.	Boolean	`true`
`sql_stats_automatic_partial_collection_enabled`	Enable automatic collection of for this table.	Boolean	`true`
`sql_stats_automatic_partial_collection_min_stale_rows`	Minimum number of stale rows that triggers for this table.	Integer	100
`sql_stats_automatic_partial_collection_fraction_stale_rows`	Target fraction of stale rows that triggers for this table.	Float	0.05
`sql_stats_forecasts_enabled`	Enable collection for this table.	Boolean	`true`

The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:

autovacuum_enabled
fillfactor

For the list of storage parameters that affect how works, see the list of .

Required privileges

The user must be a member of the or roles, or have the on the table.

Examples

Create a table with row-level TTL enabled

CREATE TABLE ttl_test (
  id UUID PRIMARY KEY default gen_random_uuid(),
  description TEXT,
  inserted_at TIMESTAMP default current_timestamp()
) WITH (ttl_expire_after = '3 months');

SHOW CREATE TABLE ttl_test;

  table_name |                                                                                         create_statement
-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  ttl_test   | CREATE TABLE public.ttl_test (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     description STRING NULL,
             |     inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP,
             |     crdb_internal_expiration TIMESTAMPTZ NOT VISIBLE NOT NULL DEFAULT current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL ON UPDATE current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL,
             |     CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC)
             | ) WITH (ttl = 'on', ttl_expire_after = '3 mons':::INTERVAL, ttl_job_cron = '@hourly')
(1 row)

In this case, CockroachDB implicitly added the ttl and ttl_job_cron .

Statements

Syntax

Data Types

Spatial Data

Constraints

WITH (storage parameter)

Syntax

Command parameters

Storage parameters

Index parameters

Table parameters

Required privileges

Examples

Create a table with row-level TTL enabled

See also

​Syntax

​Command parameters

​Storage parameters

​Index parameters

​Table parameters

​Required privileges

​Examples

​Create a table with row-level TTL enabled

​See also

Syntax

Command parameters

Storage parameters

Index parameters

Table parameters

Required privileges

Examples

Create a table with row-level TTL enabled

See also