Data recovery with branching

Remove duplicates from the shoes table

You received an email from a colleague asking you to clean the data in the shoes table, because there is some some duplicate data on the front-end.

When you ran the SELECT id, model, color FROM shoes query, you noticed there were indeed a few duplicate rows.

| # |         model        | color                                          |
|---|----------------------|------------------------------------------------|
| 1 | Air Zoom Alphafly    | Scream Green/Bright Crimson/Honeydew/Black     |
| 2 | Air Zoom Alphafly    | Scream Green/Bright Crimson/Honeydew/Black     |

To fix that issue, let’s remove duplicates from the shoes tables.

Run the remove duplicates query on a branch

Run the following query to see duplicates:

SELECT model, color, COUNT(*) 
FROM (select brand, model, description, color from shoes) AS s
GROUP BY brand, model, description, color
HAVING COUNT(*) > 1

| # |         model        | color                                        | count |
|---|----------------------|----------------------------------------------|-------|
| 1 | Invincible Run 2     | Black/Summit White/Summit White              | 2     |
| 2 | Pegasus 39           | Black/Ashen Slate/Cobalt Bliss/White         | 2     |
| 3 | Air Zoom Pegasus 39  | Black/Thunder Blue/Citron Pulse/Hyper Royal  | 2     |

Now run the following command to delete duplicates:

DELETE FROM shoes a USING shoes b
WHERE a.id > b.id

And run SELECT * FROM shoes to see the result:

| # |         model        | color                                        |
|---|----------------------|----------------------------------------------|
| 1 | Air Zoom Alphafly    | Scream Green/Bright Crimson/Honeydew/Black   |

Whoops! 😬

It looks like we used the wrong command and accidentally deleted the majority of our data. The whole team is in a panic because you ran the command on production data and now the only available shoe on the website is the Air Zoom Alphafly.

But don’t worry. You got this!

Recover the lost data

You can use Neon branching to recover the lost data in seconds. All you need to do is to check on the SQL Editor what time you performed the DELETE operation.

Now, let’s move to the Branches page and click on New Branch

You can optionally give a name to your branch. Make sure that the parent branch is main and scroll down to the Time option.

You should see a message similar to the one below to indicate that you successfully have created a new branch.

Let’s now get back to the SQL Editor in the Neon COnsole and run the following query:

SELECT * FROM shoes

Make sure you select the newly created branch (the backup branch in the example below).

| # |         model        | color                                          |
|---|----------------------|------------------------------------------------|
| 1 | Air Zoom Alphafly    | Scream Green/Bright Crimson/Honeydew/Black     |
| 2 | Air Zoom Alphafly    | Scream Green/Bright Crimson/Honeydew/Black     |
| 3 | Air Zoom Alphafly    | Total Orange/Bright Crimson/Ghost Green/Black  |

Phew 😮‍💨! You successfully recovered the data 🎉!

What have we seen here?

To recover the data, we introduced a powerful concept: branching.

Neon Branching is a copy of your data that you can create from Head (the most up-to-date state of the database), Time (from a date and time, similar to the example above). or Log-Sequence-Number (LSN).

An LSN is a unique identifier that is assigned to each transaction in the database, and is used to track the order in which transactions are committed to the database.