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

2022-09-24 Thread Adrian Klaver
On 9/24/22 09:04, Barry Kimelman wrote: On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus > wrote: > On Sep 24, 2022, at 08:49, Barry Kimelman mailto:blkimel...@gmail.com>> wrote: > I thought the whole point of ON CONFLICT DO UPDATE was so that you co

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

2022-09-24 Thread David G. Johnston
On Sat, Sep 24, 2022 at 8:49 AM Barry Kimelman 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 > Nope, the words "DO UPDATE" mean "DO an UPDATE command instead of failing for the CONFLICTing INSERT command". As m

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

2022-09-24 Thread Christophe Pettus
> On Sep 24, 2022, at 09:04, Barry Kimelman 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 da

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

2022-09-24 Thread Barry Kimelman
On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 08:49, Barry Kimelman 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 existi

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

2022-09-24 Thread Christophe Pettus
> On Sep 24, 2022, at 08:49, Barry Kimelman 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 a

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

2022-09-24 Thread Barry Kimelman
On Sat, Sep 24, 2022 at 10:44 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 08:29, Barry Kimelman 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 previousl

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

2022-09-24 Thread Christophe Pettus
> On Sep 24, 2022, at 08:29, Barry Kimelman 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 colu

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

2022-09-24 Thread Barry Kimelman
On Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 07:29, Barry Kimelman 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 (i

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

2022-09-24 Thread Christophe Pettus
> On Sep 24, 2022, at 07:29, Barry Kimelman 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 ne

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

2022-09-24 Thread Barry Kimelman
On Fri, Sep 23, 2022 at 4:42 PM Steve Baldwin wrote: > You need to prefix the rhs column(s) with 'excluded.'. For example: > > on conflict (company_name) do update set company_name = > concat(excluded.company_name,'++',excluded.string_company_id); > > On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelma

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

2022-09-23 Thread Barry Kimelman
On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman > wrote: > >> >> CREATE UNIQUE INDEX my_companies_id_unique ON >> my_companies(string_company_id) WHERE delete_timestamp IS NULL; >> CREATE UNIQUE INDEX my_compa

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

2022-09-23 Thread David G. Johnston
On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman 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 is

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

2022-09-23 Thread Steve Baldwin
You need to prefix the rhs column(s) with 'excluded.'. For example: on conflict (company_name) do update set company_name = concat(excluded.company_name,'++',excluded.string_company_id); On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelman wrote: > > I have not been able to get the "ON CONFLICT" clau

problem with on conflict / do update using psql 14.4

2022-09-23 Thread Barry Kimelman
I have not been able to get the "ON CONFLICT" clause to work I am using psql 14.4 on ubuntu Given the following table definition CREATE TABLE my_companies ( company_id SERIAL NOT NULL PRIMARY KEY, second_id INTEGER NOT NULL REFERENCES big_list(second_id), string_company_id TEXT NOT NULL,