From: | Barry Kimelman <blkimelman(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: problem with on conflict / do update using psql 14.4 |
Date: | 2022-09-23 22:07:25 |
Message-ID: | CAMPa0rVHO+pBctN0efEBvS9Rbn-jGZDg5qVE7fJgfvPyzxnCYQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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.
>
>
Thanks for the reply. but could you be more specific about the where clause
? I am new to postgresql and ON CONFLICT
--
====================
Barry Kimelman
Winnipeg, MB, Canada
From | Date | Subject | |
---|---|---|---|
Next Message | Barry Kimelman | 2022-09-24 14:29:13 | Re: problem with on conflict / do update using psql 14.4 |
Previous Message | David G. Johnston | 2022-09-23 21:43:04 | Re: problem with on conflict / do update using psql 14.4 |