Skip to main content

​
Syntax

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

ALTER TABLE table_name
    ADD [ COLUMN ] column_name data_type [ DEFAULT default_expr ];
Parameter or clauseDescription
ADD [ COLUMN ]This clause adds a new column to the table. COLUMN is optional.
column_nameSpecify the name of the column you want to add.
data_typeThe data type of the new column.
DEFAULTThe 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_exprdefault_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.
-- 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.

​
DROP COLUMN

ALTER TABLE table_name
    DROP [ COLUMN ] [ IF EXISTS ] column_name;
Parameter or clauseDescription
DROP [ COLUMN ]This clause drops an existing column from a table. COLUMN is optional.
IF EXISTSDo not return an error if the specified column does not exist. A notice is issued instead.
column_nameSpecify the column you want to remove.
-- 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.
ALTER TABLE table_name
    ALTER [ COLUMN ] column_name TYPE new_data_type;
Parameter or clauseDescription
ALTER [COLUMN]This clause modifies the data type of an existing column. COLUMN is optional.
column_nameSpecify the name of the column whose data type you want to change.
TYPEKeyword to specify that you want to change the column’s data type.
new_data_typeThe new data type for the column. This operation is primarily designed for composite types (struct types) where you can add or remove fields.
-- Change the data type of a struct column to add a new field
ALTER TABLE employees ALTER COLUMN address TYPE STRUCT<street VARCHAR, city VARCHAR, zipcode VARCHAR>;

-- Change the data type of a nested struct column
ALTER TABLE orders ALTER COLUMN customer_info TYPE STRUCT<name VARCHAR, contact STRUCT<email VARCHAR, phone VARCHAR, address VARCHAR>>;
  • 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

ALTER TABLE table_name
    OWNER TO new_user;
Parameter or clauseDescription
OWNER TOThis 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_userSpecify the user you want to assign to the table.
-- 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

ALTER TABLE table_name
    SET SCHEMA schema_name;
Parameter or clauseDescription
SET SCHEMAThis clause moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well.
schema_nameSpecify the schema to which the table will be moved.
-- Move a table named "test_table" into a schema named "test_schema"
ALTER TABLE test_table SET SCHEMA test_schema;

​
SET PARALLELISM

ALTER TABLE table_name
SET PARALLELISM { TO | = } parallelism_number;
Parameter or clauseDescription
SET PARALLELISMThis clause controls the degree of parallelism for the targeted streaming job.
parallelism_numberCan 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.
ALTER TABLE test_table SET PARALLELISM = 8;
Here is a more detailed example for you to practise this clause: First, let’s set the parallelism to 3 by the SET command. 
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 table. 
-- Create a table.
CREATE TABLE t(v int);
-- View parrellelism by rw_fragments table.
SELECT fragment_id, parallelism FROM rw_fragments;

------RESULTS
 fragment_id | parallelism
-------------+-------------
           1 |           3
           2 |           3
(2 rows)
Now we can use SET PARALLELISM to change the parallelism and view the change: 
-- 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)

-- 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

ALTER TABLE name
SWAP WITH target_name;
ParameterDescription
nameThe current name of the table to swap.
target_nameThe target name of the table you want to swap with.
-- Swap the names of the products table and the inventory table.
ALTER TABLE products
SWAP WITH inventory;

​
RENAME TO

ALTER TABLE table_name
    RENAME TO new_name;
Parameter or clauseDescription
RENAME TOThis clause changes the name of the table.
new_nameThe new name of the table.
-- Change the name of the table named "table0" to "table1"
ALTER TABLE table0 RENAME TO table1;

​
REFRESH SCHEMA

ALTER TABLE table_name
    REFRESH SCHEMA;
This command alters the schema registry of a table created with connectors. 
-- 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

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.
Example
-- 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
Example
-- Pause the source
ALTER TABLE kafka_source SET source_rate_limit TO 0;
Example
-- Alter the rate limit of this table
ALTER TABLE kafka_source SET source_rate_limit TO 1000;

​
SET BACKFILL_RATE_LIMIT

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.
Examples
-- 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;
⌘I