Re: problem with on conflict / do update using psql 14.4

On 9/24/22 09:04, Barry Kimelman wrote:
> 
> On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus <xof@thebuild.com 
> <mailto:xof@thebuild.com>> wrote:
> 
> 
> 
>      > On Sep 24, 2022, at 08:49, Barry Kimelman <blkimelman@gmail.com
>     <mailto:blkimelman@gmail.com>> wrote:
>      > I thought the whole point of ON CONFLICT DO UPDATE was so that
>     you could modify the data so that it would be inserted
> 
>     ON CONFLICT DO UPDATE allows you to modify the existing row that
>     conflicted with the row being inserted, but it won't allow you to do
>     change a row to resolve a conflict, and then insert it.
> 
> 
> Unfortunately, that is exactly what I need to do, I need to modify the 
> data on the 2nd request so that it also gets inserted.
> This is a business requirement. I need to insert all of the data, 
> including the ones with conflicts

Do the conflicting inserts need to be in the same table?

If not you could a trigger function that inserts the conflicts to 
another table and then UNION that table to the primary for query purposes.

-- 
Adrian Klaver
adrian.klaver@aklaver.com