ALTER TABLE

On this page
Related contents

Changes a table’s name, columns, keys, or retention policy.

Syntax

ALTER TABLE [ IF EXISTS ] <table_name>
  RENAME TO <new_table_name>

ALTER TABLE [ IF EXISTS ] <table_name>
  ADD COLUMN <col_name> <col_type> [ AS (<expr>) ] [ COMMENT '<string_literal>' ]

ALTER TABLE [ IF EXISTS ] <table_name>
  ALTER COLUMN <col_name> SET EXPRESSION AS (<expr>)

ALTER TABLE [ IF EXISTS ] <table_name>
  DROP COLUMN <col_name>

ALTER TABLE [ IF EXISTS ] <table_name>
  DISTINCT ON <expr> [, ...] [ BY <order_by_expr> [, ...] ]

ALTER TABLE [ IF EXISTS ] <table_name>
  DROP DISTINCT KEY

ALTER TABLE [ IF EXISTS ] <table_name>
  PARTITION BY <expr> [, ...]

ALTER TABLE [ IF EXISTS ] <table_name>
  DROP PARTITION KEY

ALTER TABLE [ IF EXISTS ] <table_name>
  CLUSTER BY <expr> [, ...]

ALTER TABLE [ IF EXISTS ] <table_name>
  DROP CLUSTER KEY

ALTER TABLE [ IF EXISTS ] <table_name>
  SET DATA RETENTION <days> DAY

ALTER TABLE [ IF EXISTS ] <table_name>
  DROP DATA RETENTION

Each statement makes one change. IF EXISTS skips the operation if the table is missing. See CREATE TABLE for partition, cluster, and deduplication keys.

Computed columns

ADD COLUMN ... AS (<expr>) adds a read-only computed column that applies to existing and new rows. ALTER COLUMN ... SET EXPRESSION AS (<expr>) replaces an existing computed column's expression while keeping its name and declared type. Evaluation or conversion errors produce NULL.

See computed columns for expression rules, Catalog definitions, write restrictions, indexes, and dependencies.

Data retention

SET DATA RETENTION sets the retention period to a whole number from 1 through 1825 days. DROP DATA RETENTION removes the policy. It does not restore data already removed by that policy.

Create a separate table to try retention changes:

CREATE TABLE retention_example (event_time timestamp, event string)

Set its retention period to 90 days:

ALTER TABLE retention_example SET DATA RETENTION 90 DAY

Remove the policy:

ALTER TABLE retention_example DROP DATA RETENTION

See the data retention guide for deletion timing and policy inspection.