RESET (storage parameter)

On this page Carat arrow pointing down

The RESET (storage parameter) statement reverts the value of a storage parameter on a table to its default value.

Note:

The RESET (storage parameter) is a subcommand of ALTER TABLE.

To reset a storage parameter on an existing index, you must drop and recreate the index without the storage parameter.

Syntax

alter_table_reset_storage_param ::=

ALTER TABLE IF EXISTS table_name RESET ( storage_parameter_key , )

Command parameters

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

Storage parameters

Table parameters

Parameter name Description Data type Default value
exclude_data_from_backup Excludes the data in this table from any future backups. Boolean false

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

  • autovacuum_enabled
  • fillfactor

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the table.

Examples

Reset a storage parameter

Following the example in WITH (storage parameter), the ttl_test table has three TTL-related storage parameters active on the table:

icon/buttons/copy
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)

To remove these settings, run the following command:

icon/buttons/copy
ALTER TABLE ttl_test RESET (ttl);
icon/buttons/copy
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,
             |     CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC)
             | )
(1 row)

See also


Yes No
On this page

Yes No