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` .	No
`shard_columns`	The first key column or columns to use when computing the shard column for a . The value must match a prefix of the index key columns. If this parameter is omitted, CockroachDB hashes all index key columns.	Tuple of column identifiers or a single column identifier	All index key columns	No
`skip_unique_checks`	Disables for indexes with columns, including indexes on tables. This avoids expensive cross-partition uniqueness checks in multi-region deployments. Can only be set on unique indexes with implicit partitioning. Warning: This should only be used if the application can guarantee uniqueness. Safe use cases include externally generated UUIDs, or columns using or as defaults (provided you do not also manually insert values into those columns). Incorrectly applying this parameter when uniqueness is not guaranteed could result in logically duplicate keys in different partitions of a unique index. Note that, by default, the optimizer does not perform uniqueness checks on columns using .	Boolean	`false`	Yes
`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`.	No
`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`.	No
`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)`.	No
`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)`.	No
`s2_level_mod`	`s2_max_level` must be divisible by `s2_level_mod`. `s2_level_mod` must be between `1` and `3`.	Integer	`1`	No
`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`	No
`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`	No

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. By default, CockroachDB attempts to automatically unset this parameter before performing many schema changes and reapplies it when done. To require manual unlocks for all DDL on schema-locked tables, set to `false`. 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. `sql_stats_automatic_collection_enabled` must be `true`.	Boolean	`true`
`sql_stats_automatic_partial_collection_enabled`	Enable automatic collection of for this table. `sql_stats_automatic_collection_enabled` must be `true`.	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_canary_window`	How long statistics for this table remain in before being promoted to stable status.	Duration	0
`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