SQL— inserting only unique values in a UNIQUE table

Insert a value into a table if it doesn’t already exist

We only want unique values in our table (image by Darius Cotoi on Unsplash)

Imagine we are a restaurant that sells all kinds of dishes. Because we’re smart we keep track of all of our ingredients in a database. The ingredients table will contain only unique ingredients that each have their own id.

The goal of this article is to create a query that inserts unique ingredients in to the table that are not already in the table. Other tables can refer to the ingredient_id so that we can filter and join on integers, which is a lot faster in large datasets.

1. Creating the table

First we’ll create the table that’ll contain the ingredients. This article uses Postgres but the same technique applies to all relational databases. The ingredients table:

As you can see we have two columns:

• Id. Since this id is a serial primary key it auto increments on insertion. We don’t have to pass this id to the table, the table creates it for us
• ingredient. Ingredient names. This column has a uniqueconstraint; this means that it can only contain unique values.

2 Inserting ingredients

The next step is to write some code that will only insert new ingredients into the ingredients table:

Let’s walk through this code:

1. First we define the data that we want to input, this happens in the WITH clause. Think of this as a table called inputvalues with the column ingredient. We put 6 ingredients in inputvalues.
2. We are going to INSERT the inputvalues into the public.ingredients table
3. We’ll filter the…