When I first saw the word upsert, I thought it was a typographical error.  

It turns out that upsert--a portmanteau of Update and Insert--is indeed a thing and an important concept to understand.

I've explained how the COPY command appends new data to the end of a table without modifying existing rows.  

This is great for adding new data.  

However, what happens when a table needs to have existing data updated as well as new data added at the same time?

The COPY command only appends data to existing tables.  It does not have any built-in logic to check for existing records.

To make things more complicated, it's possible--by design--to have duplicate data in a Redshift database.

For relational databases the opposite is true; using primary keys ensures that data is not duplicated.

However, in Amazon Redshift, while tables can have primary keys they are NOT enforced.  

Put simply, in Amazon Redshift, primary keys are ignored and loading data with the COPY command can--and often does--result in duplicates.

To prevent data duplication, perform an UPSERT operation programmatically using SQL.  

An UPSERT merges data by updating existing records and adding new ones.

Some relational databases support a single UPSERT command.  Redshift does not.  It must be done programmatically using temporary staging tables.  

The process works like this...  

• Create a temporary table configured like the destination table in Redshift.
• COPY the records to this staging table.
• JOIN the staging table with the target table.
• UPDATE the existing data in the Redshift table.
• INSERT new data to the Redshift table.
• Drop the staging table.

The trick--if you can call it that--is to do this all at once with a single COMMIT to Redshift.

To illustrate, I've created some simple SQL that walks the process with a simple SALES table.

While SQL convention is to use all capital letters, it is not required.  

To aid with some of the visuals and distinguish between the permanent Redshift table and the temporary staging table, I've kept the SQL statement and Redshift tables in UPPERCASE and the temporary tables lowercase.

This is what it looks like.

Let's go through this SQL code together.

The double-dash (--) is a single-line comment.  Anything after them is ignored.

The BEGIN keyword starts a statement block.  

SQL statement blocks are used when a number of statements need to be treated as a one transaction with a single commit.  

Statement blocks are sometimes called a batch.   

The SQL statements run but are not committed to Redshift until either an END or COMMIT statement is reached. 

Inside the statement block, the statement...

CREATE TEMPORARY TABLE temp_sales (LIKE SALES);

...does exactly what it sounds like it is doing.  

It creates a temporary table called temp_sales.  

The LIKE statement means it inherits its schema information from the SALES table.  This includes the distribution key and sort key attributes.

The COPY statement loads data into the staging table.  In this example, it's being loaded from S3.

The UPDATE statement compares temp_sales to SALES.

This statement includes an alias.  Aliases are optional.  The AS keyword is optional too.

UPDATE SALES AS S

...is equivalent to…

UPDATE SALES S

I like having the AS keyword because it makes the SQL more readable.  

Someday, someone is probably going to look at your code to update it, so why not make their life a little easier?

The UPDATE statement compares the productid fields in the two tables and then updates the matching records in the Redshift SALES table.

Then, the INSERT statement does a LEFT JOIN to make some magic happen.  

The LEFT JOIN returns all the rows from the left table and the matching rows from the right table.  

The magic needed is to find the data in the staging table that does not exist in the Redshift table.

It's a fancy SQL-based Venn diagram.  Rather than the overlap, I need to know what's missing.

After the JOIN, if there is no match, the result is NULL.  

Using those NULL fields, the INSERT operation adds the new data from the staging table.

Magic!

As the saying goes:

Any sufficiently advanced technology is indistinguishable from magic.

--Arthur C. Clarke

When the END statement is reached, the statement block is closed and changes are committed to Redshift as a single transaction.  

If it fails, Redshift will automatically roll back the transaction to maintain the integrity of the database.

Also, I want to point out that, while the merge operation is a single transaction, the creating and dropping of the temporary staging table is separate from it.

Sorting and vacuuming are expensive operations.   I mention this fact because they should be done only when needed.

As it happens, if you use an UPSERT method to COPY new data into a table, the table needs to be sorted.

Since UPSERT performs a DELETE, it may leave the table in an unsorted state.  When a record is deleted from a Redshift table, it is only marked for deleting, it is not automatically removed.