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

> On Sep 24, 2022, at 07:29, Barry Kimelman <blkimelman@gmail.com> wrote:
>
> CREATE UNIQUE INDEX my_companies_company_name_unique ON my_companies(company_name) WHERE delete_timestamp IS NULL;

The issue here is that the unique index is partial (it has a WHERE clause).  In order to use that as an arbiter, you
needinclude a WHERE clause in the ON CONFLICT clause that matches the one on the index. 

I believe that something like:

insert into my_companies (second_id,string_company_id,company_name,person_name)
values (1,'66','widgets','seller-toto')
on conflict (company_name) where delete_timestamp IS NULL do update set company_name =
concat(company_name,'++',string_company_id)

... will work.  Note that if you do an insert with a duplicate "company_name", but "delete_timestamp" not null, it
*won't*treat that as a conflict and won't run the ON CONFLICT action; it'll just insert the row. 

> On Sep 24, 2022, at 08:29, Barry Kimelman <blkimelman@gmail.com> wrote:
> Thanks for the response. When I ran the INSERT with your suggested change I got an error message telling me
> "column reference 'company_name' is ambiguous"

As previously noted, you'll need to do both: add "excluded." to qualify the column names in the UPDATE.  Here's a
contrivedexample: 

xof=# create table t(i integer, j integer, k integer);
CREATE TABLE
xof=# create unique index on t(i) where j != 0;
CREATE INDEX
xof=# insert into t(i, j, k) values(1, 2, 3) on conflict (i) do update set k=k+1;
ERROR:  column reference "k" is ambiguous
LINE 1: ..., j, k) values(1, 2, 3) on conflict (i) do update set k=k+1;
xof=# insert into t(i, j, k) values(1, 2, 3) on conflict (i) do update set k=excluded.k+1;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
xof=# insert into t(i, j, k) values(1, 2, 3) on conflict (i) where j != 0 do update set k=excluded.k+1;
INSERT 0 1

> On Sep 24, 2022, at 08:49, Barry Kimelman <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
allowyou to do change a row to resolve a conflict, and then insert it. 

> On Sep 24, 2022, at 09:04, Barry Kimelman <blkimelman@gmail.com> wrote:
> 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.

The best approach is to optimistically insert the row, catch the error if there's a conflict, and modify the data and
tryagain.  If a row with the same conflict can come from multiple sessions, you'll need to decide how to handle the
casethat some other session "wins" and inserts the row, and you get a conflict again. 

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