Insert VALUES not already in table

Question

I want to create a table and then initialize it with some values, in as concise manner as possible.

However, this script gets executed every time my app starts, so the insert should happen only on items that were not already added previously.

I do not want to use IGNORE directive in 'INSERT IGNORE INTO', because I do not want to ignore unexpected errors.

For some reason, INSERT INTO fails with "SQL error (1136): Column count doesn't match value count at row 1", even though the select that follows gives the values that need to be added.

Here's the failing code:

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `privileges` (
    `id` TINYINT NOT NULL AUTO_INCREMENT,
    `label` VARCHAR(25) UNIQUE,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `privileges` (`label`)
SELECT `label` FROM (
SELECT NULL AS `label`
UNION VALUES
    ('item1'),
    ('item2')
) X
WHERE `label` IS NOT NULL
AND `label` NOT IN (SELECT `label` FROM `privileges`)

COMMIT;

Currently I am solving this by first inserting the values into a temporary table, and then performing a select on that. But why isn't the above working and is there a more concise way to do what I'm trying to do?

I'm using MariaDB 10.3.9, added missing UNIQUE constraint

Edit 2: Thanks to LukStorms for figuring out the error was related to AUTO_INCREMENT, it seems passing NULL for AUTO_INCREMENT column solves the problem like so:

INSERT INTO `privileges` (id, label)
WITH ITEMS(label) AS (VALUES
    ('users:read'),('users:create'),
    ('clients:read'),('clients:write'),
    ('catalog:read'),('catalog:write'),
    ('cart:read'),('cart:write'),
    ('orders:read'),('orders:write'), ('test1')
) SELECT NULL, label FROM ITEMS i
WHERE label NOT IN (SELECT label FROM `privileges`);

Answer 1

First, your application is trying to double-insert values. It probably shouldn't be doing that (though I can think of a few valid use cases). Consider making it so that it does not try to add data that it's already added before. If you don't have easy access to inter-instance state, pull the current list out of the database on startup before deciding what to insert.

Second, if you want labels to be unique, why is there not a unique key on the label field? At the moment, INSERT IGNORE wouldn't even work because there is nothing in your schema preventing duplicate label values. I would ask yourself why you need an auto-incrementing ID: why not just have the label, and make it the primary key?

Then, if you still need to do this duplicate-elision at the SQL layer, you may use ON DUPLICATE KEY to suck up redundant inserts of an existing primary key:

INSERT INTO `privileges` (`label`)
VALUES
    ('item1'),
    ('item2')
)
ON DUPLICATE KEY UPDATE `label` = `label`

This solution is difficult to implement with your auto-increment ID key, because your application probably doesn't know what the ID is going to be. Another reason to consider dropping it.

Unfortunately, there's no ON DUPLICATE KEY IGNORE.

If you want to keep the ID key, and you don't want your application to do a read step on startup (perhaps for scalability reasons), then INSERT IGNORE to be quite honest is your best bet, though you're still going to need at least a unique key on label to make that work.

Answer 2

In MariaDb 10.3+, using a CTE with a the VALUES expression can let you assign a column name to it.

with ITEMS(label) as
(VALUES 
 ('item1')
,('item2'))
select i.label 
from ITEMS i
where not exists (select 1 from privileges p where p.label = i.label)

But somehow it gives an error when inserting into a table that has a field with an AUTO_INCREMENT. Seems like a bug to me.

However, when you insert a NULL into a an AUTO_INCREMENT field then the NULL gets ignored. But you discovered that behaviour yourself.

So this works:

INSERT INTO privileges (id, label)
WITH ITEMS(label) as (
 VALUES ('item1'), ('item2')
)
SELECT null, i.label
FROM ITEMS i
WHERE NOT EXISTS (SELECT 1 FROM privileges p WHERE p.label = i.label);

Test on db<>fiddle here

Using unioned selects also works though.

INSERT INTO privileges (label)
SELECT label
FROM (
 SELECT 'item1' as label UNION ALL 
 SELECT 'item2'
) i
WHERE NOT EXISTS (SELECT 1 FROM privileges p WHERE p.label = i.label);

db<>fiddle here

Maybe another way is to use a temporary table (that will vanish when the session expires)

CREATE TEMPORARY TABLE tmp_items (label VARCHAR(25) NOT NULL PRIMARY KEY);

INSERT INTO tmp_items (label) VALUES 
 ('item1')
,('item2');

INSERT INTO privileges (label)
SELECT label
FROM tmp_items i
WHERE label NOT IN (SELECT DISTINCT label FROM privileges);

Test on db<>fiddle here