> ## Documentation Index > Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt > Use this file to discover all available pages before exploring further. # ALTER TABLE > The `ALTER TABLE` command modifies the definition of a table. ## Syntax ```sql theme={null} ALTER TABLE table_name alter_option; ``` alter\_option depends on the operation you want to perform on the table. For all supported clauses, see the sections below. ## Clauses ### `ADD COLUMN` ```sql theme={null} ALTER TABLE table_name ADD [ COLUMN ] column_name data_type [ DEFAULT default_expr ]; ``` | Parameter or clause | Description | | :------------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | **ADD \[ COLUMN ]** | This clause adds a new column to the table. COLUMN is optional. | | *column\_name* | Specify the name of the column you want to add. | | *data\_type* | The data type of the new column. | | **DEFAULT** | The DEFAULT clause allows you to assign a default value to a column. This default value is used when a new row is inserted, and no explicit value is provided for that column. | | *default\_expr* | default\_expr is any constant value or variable-free expression that does not reference other columns in the current table or involve subqueries. The data type of default\_expr must match the data type of the column.If default\_expr is impure, such as using a function like now(), all historical data will be filled with the result of the expression evaluated at the time the statement was executed. For future insertions, the default expression will be evaluated at the time of each respective insertion. | ```sql theme={null} -- Add a column named "age" to a table named "employees" with a data type of integer ALTER TABLE employees ADD age int; ``` * If your table is defined with a schema registry, you can change the table schema by `ALTER TABLE t REFRESH SCHEMA` or `ALTER TABLE ADD COLUMN`. * Columns added by this command cannot be used by any existing materialized views or indexes. You must create new materialized views or indexes to reference it. ### `CONNECTOR WITH` Added in v2.6.0. ```sql theme={null} ALTER TABLE table_name CONNECTOR WITH ( 'property_name' = 'value', ... ); ``` | Parameter | Description | | :----------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **CONNECTOR WITH** | Allows you to update connector-specific properties for an existing table without recreating it. Be aware that the properties defined in CONNECTION should be altered by `ALTER CONNECTION`. | | property\_name | We currently support altering Kafka connectors only. Support for more connectors and properties will be added in future release. For Kafka, supported parameters include: `properties.client.id`, `properties.sync.call.timeout`, `properties.enable.auto.commit`, `properties.enable.ssl.certificate.verification`, `properties.fetch.max.bytes`, `properties.fetch.queue.backoff.ms`, `properties.fetch.wait.max.ms`, `properties.message.max.bytes`, `properties.queued.max.messages.kbytes`, `properties.queued.min.messages`, `properties.receive.message.max.bytes`, `properties.statistics.interval.ms`, `properties.ssl.endpoint.identification.algorithm`, `properties.ssl.ca.location`, `properties.ssl.ca.pem`, `properties.ssl.certificate.location`, `properties.ssl.certificate.pem`, `properties.ssl.key.location`, `properties.ssl.key.pem`, `properties.ssl.key.password`, `properties.security.protocol`, `properties.sasl.mechanism`, `properties.sasl.username`, `properties.sasl.password` | ```sql Example theme={null} ALTER TABLE plain_students CONNECTOR WITH ( 'properties.security.protocol' = 'SASL_SSL' ); ``` ### `DROP CONNECTOR` ```sql theme={null} ALTER TABLE table_name DROP CONNECTOR; ``` Removes the connector definition from a table that was created with a connector. The table keeps the same schema and existing data, but ingestion from the upstream source stops. This clause applies to tables created with connector settings, including CDC tables and tables that use a schema registry. It does not apply to plain tables created without a connector, and it does not apply to standalone `SOURCE` objects. After you drop the connector, the table becomes a plain `CREATE TABLE ...` and there is no supported way to add a connector back with `ALTER TABLE`. If you need to resume ingestion, recreate the table with the connector configuration you need. ```sql Example theme={null} -- Stop ingesting from Kafka but keep the table and its data. ALTER TABLE orders DROP CONNECTOR; ``` ### `DROP COLUMN` ```sql theme={null} ALTER TABLE table_name DROP [ COLUMN ] [ IF EXISTS ] column_name; ``` | Parameter or clause | Description | | :------------------- | :----------------------------------------------------------------------------------------- | | **DROP \[ COLUMN ]** | This clause drops an existing column from a table. COLUMN is optional. | | **IF EXISTS** | Do not return an error if the specified column does not exist. A notice is issued instead. | | *column\_name* | Specify the column you want to remove. | ```sql theme={null} -- Remove a column named "fax" from the "employees" table ALTER TABLE employees DROP fax; ``` * If your table is defined with a schema registry, you can change the table schema by `ALTER TABLE t REFRESH SCHEMA` or `ALTER TABLE DROP COLUMN`. * You cannot drop columns referenced by materialized views or indexes. * To drop a column referenced by a generated column, you must first drop the generated column. ### `ALTER COLUMN TYPE` Added in v2.5.0. ```sql theme={null} ALTER TABLE table_name ALTER [ COLUMN ] column_name TYPE new_data_type; ``` | Parameter or clause | Description | | :------------------ | :-------------------------------------------------------------------------------------------------------------------------------------------- | | **ALTER \[COLUMN]** | This clause modifies the data type of an existing column. COLUMN is optional. | | *column\_name* | Specify the name of the column whose data type you want to change. | | **TYPE** | Keyword to specify that you want to change the column's data type. | | *new\_data\_type* | The new data type for the column. This operation is primarily designed for composite types (struct types) where you can add or remove fields. | ```sql theme={null} -- Change the data type of a struct column to add a new field ALTER TABLE employees ALTER COLUMN address TYPE STRUCT; -- Change the data type of a nested struct column ALTER TABLE orders ALTER COLUMN customer_info TYPE STRUCT>; ``` * This operation is primarily supported for composite types (struct types). You can add or remove fields from struct types, and struct types can be nested within other composite types. * New fields added to a struct type cannot be referenced by existing materialized views or indexes. You must create new materialized views or indexes to reference the new fields. * If you remove fields referenced by downstream queries, those fields will return `NULL` in the query results. ### `OWNER TO` ```sql theme={null} ALTER TABLE table_name OWNER TO new_user; ``` | Parameter or clause | Description | | :------------------ | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **OWNER TO** | This clause changes the owner of the table to the specified user. It will cascadingly change all related internal objects as well, and the associated indexes will be changed too. | | *new\_user* | Specify the user you want to assign to the table. | ```sql theme={null} -- Change the owner of the table named "t" to the user "user1" ALTER TABLE t OWNER TO user1; ``` After setting, you can observe the parallelism status within the internal `rw_table_fragments ` table. ### `SET SCHEMA` ```sql theme={null} ALTER TABLE table_name SET SCHEMA schema_name; ``` | Parameter or clause | Description | | :------------------ | :---------------------------------------------------------------------------------------------------------------------------------------- | | **SET SCHEMA** | This clause moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well. | | *schema\_name* | Specify the schema to which the table will be moved. | ```sql theme={null} -- Move a table named "test_table" into a schema named "test_schema" ALTER TABLE test_table SET SCHEMA test_schema; ``` ### `SET PARALLELISM` ```sql theme={null} ALTER TABLE table_name SET PARALLELISM { TO | = } parallelism_number; ``` | Parameter or clause | Description | | :------------------ | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **SET PARALLELISM** | This clause controls the degree of [parallelism](/reference/key-concepts#parallelism) for the targeted [streaming job](/reference/key-concepts#streaming-queries). | | parallelism\_number | Can be ADAPTIVE or a fixed number (e.g., 1, 2, 3). Setting it to ADAPTIVE expands the job's parallelism to all available units, while a fixed number locks it at that value. Setting it to 0 is equivalent to ADAPTIVE. The maximum allowed value is determined by the `max_parallelism` of the job. For more information, see [Configuring maximum parallelism](/deploy/k8s-cluster-scaling#configuring-maximum-parallelism). | ```sql theme={null} ALTER TABLE test_table SET PARALLELISM = 8; ``` Here is a more detailed example for you to practice this clause: First, let's set the parallelism to `3` by the [SET command](/operate/view-configure-runtime-parameters#how-to-configure-runtime-parameters). ```sql theme={null} SET streaming_parallelism = 3; ``` Then let's create a table to view the parallelism we set. As mentioned, the parallelism status of a table can be observed within the [`rw_fragments`](/sql/system-catalogs/rw-catalog#available-risingwave-catalogs) table. ```sql theme={null} -- Create a table. CREATE TABLE t(v int); -- View parallelism by rw_fragments table. SELECT fragment_id, parallelism, parallelism_policy FROM rw_fragments; ------RESULTS fragment_id | parallelism | parallelism_policy -------------+-------------+------------------------ 1 | 12 | inherit(adaptive) 2 | 12 | inherit(adaptive) 3 | 12 | upstream_fragment([1]) 4 | 1 | single 5 | 12 | upstream_fragment([1]) (5 rows) ``` Now we can use `SET PARALLELISM` to change the parallelism and view the change: ```sql theme={null} -- Set to a fixed number. ALTER TABLE t SET PARALLELISM = 8; SELECT fragment_id, parallelism FROM rw_fragments; ------RESULTS fragment_id | parallelism -------------+------------- 1 | 8 2 | 8 (2 rows) ``` ```sql theme={null} -- Set to ADAPTIVE ALTER TABLE t SET PARALLELISM = adaptive; SELECT fragment_id, parallelism FROM rw_fragments; ------RESULTS fragment_id | parallelism -------------+------------- 1 | 12 2 | 12 (2 rows) ``` ### `SWAP WITH` ```sql theme={null} ALTER TABLE name SWAP WITH target_name; ``` | Parameter | Description | | :------------- | :-------------------------------------------------- | | *name* | The current name of the table to swap. | | *target\_name* | The target name of the table you want to swap with. | ```sql theme={null} -- Swap the names of the products table and the inventory table. ALTER TABLE products SWAP WITH inventory; ``` ### `RENAME TO` ```sql theme={null} ALTER TABLE table_name RENAME TO new_name; ``` | Parameter or clause | Description | | :------------------ | :----------------------------------------- | | **RENAME TO** | This clause changes the name of the table. | | *new\_name* | The new name of the table. | ```sql theme={null} -- Change the name of the table named "table0" to "table1" ALTER TABLE table0 RENAME TO table1; ``` ### `REFRESH SCHEMA` ```sql theme={null} ALTER TABLE table_name REFRESH SCHEMA; ``` This command alters the schema registry of a table created with connectors. ```sql theme={null} -- Refresh the schema of the table named "t_user". ALTER TABLE t_user REFRESH SCHEMA; ``` If a downstream fragment references a column that is either missing or has undergone a type change in the updated schema, the command will be declined. ### `SET SOURCE_RATE_LIMIT` ```sql theme={null} ALTER TABLE table_name SET SOURCE_RATE_LIMIT { TO | = } { default | rate_limit_number }; ``` For tables with connector, this statement controls the rate limit of the associated source. For the specific value of `SOURCE_RATE_LIMIT`, refer to [How to view runtime parameters](/operate/view-configure-runtime-parameters#how-to-view-runtime-parameters). ```sql Example theme={null} -- Create a table with source CREATE TABLE kafka_source (v1 int) WITH ( connector = 'kafka', topic = 'kafka_source', properties.bootstrap.server = 'localhost:29092', scan.startup.mode = 'earliest', source_rate_limit = 200 ) FORMAT PLAIN ENCODE JSON ``` ```sql Example theme={null} -- Pause the source ALTER TABLE kafka_source SET source_rate_limit TO 0; ``` ```sql Example theme={null} -- Alter the rate limit of this table ALTER TABLE kafka_source SET source_rate_limit TO 1000; ``` ### `SET BACKFILL_PARALLELISM` ```sql theme={null} ALTER TABLE table_name SET BACKFILL_PARALLELISM { TO | = } parallelism_value [ DEFERRED ]; ``` This statement controls the degree of parallelism used during the backfill phase of a streaming job. It lets you tune resource usage for backfilling independently from the job's steady-state parallelism. `SET BACKFILL_PARALLELISM` is not supported for tables using the Iceberg engine. | Parameter or clause | Description | | :---------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **SET BACKFILL\_PARALLELISM** | Sets the parallelism for the backfill phase of the streaming job. | | *parallelism\_value* | Can be `ADAPTIVE` or a fixed number (e.g., 1, 2, 3). Setting it to `ADAPTIVE` or `0` expands backfill parallelism to all available units. A fixed number locks it at that value. | | **DEFERRED** | Optional. When specified, the change takes effect after the current backfill completes, without interrupting an ongoing backfill. If omitted, the change is applied immediately. | ```sql theme={null} -- Set backfill parallelism to a fixed value ALTER TABLE t1 SET BACKFILL_PARALLELISM = 4; -- Set backfill parallelism to adaptive ALTER TABLE t1 SET BACKFILL_PARALLELISM = ADAPTIVE; -- Defer the parallelism change until the current backfill completes ALTER TABLE t1 SET BACKFILL_PARALLELISM = 4 DEFERRED; ``` ### `SET BACKFILL_RATE_LIMIT` ```sql theme={null} ALTER TABLE table_name SET BACKFILL_RATE_LIMIT { TO | = } { default | rate_limit_number }; ``` For CDC table created from a CDC source, this statement controls the rate limit of backfilling from the CDC database. For the specific value of `BACKFILL_RATE_LIMIT`, refer to [How to view runtime parameters](/operate/view-configure-runtime-parameters/#how-to-view-runtime-parameters). ```sql Examples theme={null} -- Pause the backfill ALTER TABLE t1 SET BACKFILL_RATE_LIMIT=0; -- Alter backfill rate limit ALTER TABLE t1 SET BACKFILL_RATE_LIMIT=1000; -- Disable the backfill rate limit ALTER TABLE t1 SET BACKFILL_RATE_LIMIT=DEFAULT; ```