Swapping partitions

SWAP_PARTITIONS_BETWEEN_TABLES combines the operations of DROP_PARTITIONS and MOVE_PARTITIONS_TO_TABLE as a single transaction. SWAP_PARTITIONS_BETWEEN_TABLES is useful if you regularly load partitioned data from one table into another and need to refresh partitions in the second table.

For example, you might have a table of revenue that is partitioned by date, and you routinely move data into it from a staging table. Occasionally, the staging table contains data for dates that are already in the target table. In this case, you must first remove partitions from the target table for those dates, then replace them with the corresponding partitions from the staging table. You can accomplish both tasks with a single call to SWAP_PARTITIONS_BETWEEN_TABLES.

By wrapping the drop and move operations within a single transaction, SWAP_PARTITIONS_BETWEEN_TABLES maintains integrity of the swapped data. If any task in the swap operation fails, the entire operation fails and is rolled back.

Example

The following example creates two partitioned tables and then swaps certain partitions between them.

Both tables have the same definition and have partitions for various year values. You swap the partitions where year = 2008 and year = 2009. Both tables have at least two rows to swap.

1. Create the customer_info table:

   => CREATE TABLE customer_info (
         customer_id INT NOT NULL,
         first_name VARCHAR(25),
         last_name VARCHAR(35),
         city VARCHAR(25),
         year INT NOT NULL)
         ORDER BY last_name
         PARTITION BY year;
   

2. Insert data into the customer_info table:

   COPY customer_info FROM STDIN;
   Enter data to be copied followed by a newline.
   End with a backslash and a period on a line by itself.
   >> 1|Joe|Smith|Denver|2008
   >> 2|Bob|Jones|Boston|2008
   >> 3|Silke|Muller|Frankfurt|2007
   >> 4|Simone|Bernard|Paris|2014
   >> 5|Vijay|Kumar|New Delhi|2010
   >> \.
   

3. View the table data:

   => SELECT * FROM customer_info ORDER BY year DESC;
    customer_id | first_name | last_name |   city    | year
   -------------+------------+-----------+-----------+------
              4 | Simone     | Bernard   | Paris     | 2014
              5 | Vijay      | Kumar     | New Delhi | 2010
              1 | Joe        | Smith     | Denver    | 2008
              2 | Bob        | Jones     | Boston    | 2008
              3 | Silke      | Muller    | Frankfurt | 2007
   (5 rows)
   

4. Create a second table, member_info, that has the same definition as customer_info:

   => CREATE TABLE member_info LIKE customer_info INCLUDING PROJECTIONS;
   CREATE TABLE
   

5. Insert data into the member_info table:

   => COPY member_info FROM STDIN;
   Enter data to be copied followed by a newline.
   End with a backslash and a period on a line by itself.
   >> 1|Jane|Doe|Miami|2001
   >> 2|Mike|Brown|Chicago|2014
   >> 3|Patrick|OMalley|Dublin|2008
   >> 4|Ana|Lopez|Madrid|2009
   >> 5|Mike|Green|New York|2008
   >> \.
   

6. View the data in the member_info table:

   => SELECT * FROM member_info ORDER BY year DESC;
    customer_id | first_name | last_name |   city   | year
   -------------+------------+-----------+----------+------
              2 | Mike       | Brown     | Chicago  | 2014
              4 | Ana        | Lopez     | Madrid   | 2009
              3 | Patrick    | OMalley   | Dublin   | 2008
              5 | Mike       | Green     | New York | 2008
              1 | Jane       | Doe       | Miami    | 2001
   (5 rows)
   

7. To swap the partitions, run the SWAP_PARTITIONS_BETWEEN_TABLES function:

   => SELECT SWAP_PARTITIONS_BETWEEN_TABLES('customer_info', 2008, 2009, 'member_info');
                                       SWAP_PARTITIONS_BETWEEN_TABLES
   ----------------------------------------------------------------------------------------------
    1 partition values from table customer_info and 2 partition values from table member_info are swapped at epoch 1045.
   
   (1 row)
   

8. Query both tables to confirm that they swapped their respective 2008 and 2009 records:

   => SELECT * FROM customer_info ORDER BY year DESC;
    customer_id | first_name | last_name |   city    | year
   -------------+------------+-----------+-----------+------
              4 | Simone     | Bernard   | Paris     | 2014
              5 | Vijay      | Kumar     | New Delhi | 2010
              4 | Ana        | Lopez     | Madrid    | 2009
              3 | Patrick    | OMalley   | Dublin    | 2008
              5 | Mike       | Green     | New York  | 2008
              3 | Silke      | Muller    | Frankfurt | 2007
   (6 rows)
   => SELECT * FROM member_info ORDER BY year DESC;
    customer_id | first_name | last_name |  city   | year
   -------------+------------+-----------+---------+------
              2 | Mike       | Brown     | Chicago | 2014
              2 | Bob        | Jones     | Boston  | 2008
              1 | Joe        | Smith     | Denver  | 2008
              1 | Jane       | Doe       | Miami   | 2001
   (4 rows)