Return conflicting rows on batch import

[zepatrik]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe your problem

The error you get from the batch import when one identity conflicts does not specify which one caused the conflict.

Describe your ideal solution

A list of identities that cannot be imported, optionally importing all others that do not conflict.

Workarounds or alternatives

Implementing a search on which identity/identities fails to import. This is a lot of unnecessary work.

Version

latest

Additional Context

POC:

create table t (i int primary key);

with
  -- define values
  v (i, external_id) as (values (1, '1x'), (2, '2x'), (3, '3x')),
  -- insert all non-conflicting values
  tt as (
    insert into t (i)
    (select i from v)
    on conflict do nothing
    returning i
  )
-- get all values including the external ID for identification, and see which ones were inserted
select v.i, v.external_id, tt.i is null as ignored_because_conflict
from v
left join tt on v.i = tt.i;