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

On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman <blkimelman(at)gmail(dot)com> wrote:

>
> CREATE UNIQUE INDEX my_companies_id_unique ON
> my_companies(string_company_id) WHERE delete_timestamp IS NULL;
> CREATE UNIQUE INDEX my_companies_company_name_unique ON
> my_companies(company_name) WHERE delete_timestamp IS NULL;
>
> I issued the following sql insert in an attempt to use "on conflict" to
> update the
> duplicate column name
>
> insert into my_companies
> (second_id,string_company_id,company_name,person_name)
> values (1,'66','widgets','seller-toto')
> on conflict (company_name) do update set company_name =
> concat(company_name,'++',string_company_id);
>
>
> when I ran my sql statement I received the following error message
>
> bkimelman=# \i insert-companies-1c.sql
> psql:insert-companies-1c.sql:3: ERROR: column reference "company_name" is
> ambiguous
> LINE 3: ...company_name) do update set company_name = concat(company_na...
>
> I tried fully qualifying the column names in the concat() function call,
> but all that did was get me a different error message
>

Which is the more interesting one since the ambiguous column name error you
did show has been resolved...

> What would be the proper format for the "on conflict" clause ?
>

I think that since your index is partial you need to modify your insert
command's on conflict clause to include a matching where clause. (WHERE
index_predicate)

https://www.postgresql.org/docs/current/sql-insert.html

David J.