Forum Discussion
A good starting point is finding a SQL implementation - it will give you an idea of what’s possible on your database.
For instance in PostgreSQL you would want to use INSERT … ON CONFLICT (product_id) … however the only options seem to be DO NOTHING or DO UPDATE SET …. It’s not possible to raise an error (which would kill the command) or run an arbitrary stored procedure.
Instead you would need to use something like this
WITH both as (SELECT id FROM TABLE table1 INTERSECT SELECT id FROM TABLE2)
INSERT id, value INTO table VALUES (SELECT id, value FROM table2 WHERE id NOT IN both.id) AS X;
INSERT * INTO duplicates FROM (SELECT * FROM table2 WHERE id IN both.id) AS X;
(that’s off the top of my head - there may be small syntax errors.)
This is why snaps don’t always implement “obvious” features like writing duplicates to an error view. We want to keep the database snaps as consistent as possible and sometimes a feature that is easy to implement on one database would require a lot of work under the covers on other databases.
Related Content
- 11 months ago