How to use the exchange command to switch tables

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

Insert sample row

Create example temporary table that will be exchanged

Insert sample row into the temporary table

Run the EXCHANGE command to switch the tables

Test that the tables are now exchanged and show the rows are switched

Nov 21, 2024 · 2 min read

Question​

Answer​

Question

Answer