How to force an error if an "INSERT INTO ... SELECT" found zero rows (In a NpgsqlBatchedCommand)

Current code:

  var cmd = new NpgsqlBatchCommand(@"
                    INSERT INTO dc_file_structure (
                        manual_code, revision_id,
                        file_path, file_size, file_modified)
                    SELECT
                        manual_code, $2,
                        file_path, file_size, file_modified
                    FROM dc_file_structure
                    WHERE file_id = $1
                    ON CONFLICT (manual_code, revision_id, file_path) DO NOTHING;
                    ");
   cmd.Parameters.AddWithValue(CloneExistingFilePk);
   cmd.Parameters.AddWithValue(NewRevisionId);

This copies one row into a new row with all the same values except for revision_id. The ON CONFLICT makes it idempotent if it retries due to network error. So it is possible for INSERT to not do anything, and that's ok.

But I want it to throw an exception if SELECT found nothing. Right now, it succeeds silently.

It looks like 'STRICT' only works for SELECT INTO, which I'm not using.

I'm sending 10,000 of these batched commands to postgres at one time, so performance ideally doesn't suffer.

Solution

There are a number of different ways of approaching this problem. Assuming you have a not null constraint on at least one of the columns that you are going to insert into then you could select your required row with a left join on rather than using where so that you will end up with a null containing row if the select returns no rows and the insert will fail with an error.

INSERT INTO dc_file_structure (
    manual_code, revision_id,
    file_path, file_size, file_modified)
SELECT
    dc.manual_code, revision.id,
    dc.file_path, dc.file_size, dc.file_modified
FROM (SELECT $2 AS id) AS revision
LEFT JOIN dc_file_structure AS dc ON dc.file_id = $1
ON CONFLICT (manual_code, revision_id, file_path) DO NOTHING;

An alternative would be to perform the select separately from the insert in a cte then count the number of rows and throw an error if no rows were found. In this example I've used division by zero to throw the error for simplicity but you could always define a PL/pgSQL function to raise a custom error if you wished.

with old_row as (
    select * from dc_file_structure where file_id = $1
    ),
sanity_check as (
    select 1 / count(*) from old_row
    )
insert into dc_file_structure (
    manual_code, revision_id,
    file_path, file_size, file_modified)
select
    manual_code, $2,
    file_path, file_size, file_modified
from old_row
on conflict (manual_code, revision_id, file_path) do nothing;