Skip to main content

How to use the exchange command to switch tables

· 2 min read

Question​

How do I use the EXCHANGE command to switch table names?

Answer​

The EXCHANGE command is useful when you need to switch a current table with another table that is temporary where possibly Primary Keys or other settings were updated. This happens atomically vs with the RENAME command. It is also useful when you have Materialized Views triggering on a source table and are trying to avoid rebuilding the view.

Below is a simple example on how it works and how to test:

  • Create sample database
create database db1;
  • Create example table
create table db1.table1_exchange
(
 id Int32,
 string_field String
)
engine = MergeTree()
order by id;
  • Insert sample row
insert into db1.table1_exchange
values
(1, 'a');
  • Create example temporary table that will be exchanged
create table db1.table1_exchange_temp
(
 id Int32,
 string_field String
)
engine = MergeTree()
order by id;
  • Insert sample row into the temporary table
insert into db1.table1_exchange_temp
values
(2, 'b');
  • Run the EXCHANGE command to switch the tables
exchange tables db1.table1_exchange and db1.table1_exchange_temp;
  • Test that the tables are now exchanged and show the rows are switched
select * from db1.table1_exchange;
┌─id─┬─string_field─┐
│  2 │ b            │
└────┴──────────────┘

1 row in set. Elapsed: 0.002 sec.