Using INSERT effectively

For this section, we will create an index on our skiers table to ensure each skier’s name is unique:

CREATE UNIQUE INDEX skier_unique
ON skiers (skier_first_name);

With this index created, we can be assured that skier_first_name is a unique value.

Imagine that we have a new skier called Kim who has a blue helmet. We can use the conventional INSERT syntax to list the columns and values:

INSERT INTO skiers(skier_first_name, skier_helmet_color)
SELECT 'Kim' AS skier_first_name, 'blue' AS skier_helmet_color;

This isn’t very difficult, but we do need to ensure we keep the positional order of the column names (skier_first_name, skier_helmet_color) so that they match the order of the data provided ('Kim', 'blue'). This can become tedious if we are dealing with very wide tables with numerous columns and need to keep the ordering consistent.

DuckDB allows us to use the BY NAME directive to signify...