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 <mailto:x...@thebuild.com>> wrote: > On Sep 24, 2022, at 08:49, Barry Kimelman mailto:blkimel...@gmail.com>> wrote: > I thought the whole point of ON CONFLICT DO UPDAT

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

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 CONFL

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

> 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

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 UPDA

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

> 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

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

gt; INSERT 0 1 > > I added the excluded reference as you stated. I ran the INSERT and I did indeed get back a response of "INSERT 0 1" However, when I ran a select to look at the table, nothing had been inserted. I thought the whole point of ON CONFLICT DO UPDATE was so that you could modify the data so that it would be inserted

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

> 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

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

> 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

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

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

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

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

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,

Re: row level security on conflict do update

On Tue, Apr 26, 2022 at 9:44 AM alias wrote: > >> 723 >> >> -- Violates USING qual for UPDATE policy p3.

row level security on conflict do update

git.postgresql.org Git - postgresql.git/blob - src/test/regress/sql/rowsecurity.sql > 58 >

Re: ON CONFLICT DO UPDATE

On 05/09/2018 09:50 PM, tango ward wrote: Ccing list. Hi, this is the my ON CONFLICT CODE ON CONFLICT (school_system_id,   student_id,   campus_name   ) DO UPDATE   

Re: ON CONFLICT DO UPDATE

Yes, that's what I figured out eventually. I thought, only the columns that I declared inside the ON CONFLICT() parenthesis can be called in SET. My bad. On Thu, May 10, 2018 at 5:57 PM, Alban Hertroys wrote: > > > On 10 May 2018, at 7:13, tango ward wrote: > > > ON CO

Re: ON CONFLICT DO UPDATE

> On 10 May 2018, at 7:13, tango ward wrote: > ON CONFLICT (school_system_id, > student_id, > campus_name > ) DO UPDATE > SET s

Re: ON CONFLICT DO UPDATE

"student_id", >>> "campus_name" >>> ) >>> >>> Searched online and found this magical tool called ON CONFLICT DO >>> UPDATE. I played around with it, made it work but there's a problem. The >>&

Re: ON CONFLICT DO UPDATE

lance model in Djano >> have Class Meta of: >> >> class Meta: >> unique_together = ( >> "school", >> "student_id", >> "campus_name" >> ) >> >> Searc

Re: ON CONFLICT DO UPDATE

unique_together = ( >> "school", >> "student_id", >> "campus_name" >> ) >> >> Searched online and found this magical tool called ON CONFLICT DO UPDATE. >> I played around with it, made it wo

Re: ON CONFLICT DO UPDATE

model in Djano have Class Meta of: class Meta:     unique_together = (     "school",     "student_id",     "campus_name"     ) Searched online and found this magical tool called ON CONFLICT DO UPDATE. I played around with it,

ON CONFLICT DO UPDATE

Meta: unique_together = ( "school", "student_id", "campus_name" ) Searched online and found this magical tool called ON CONFLICT DO UPDATE. I played around with it, made it work but there's a problem. The balance da

testing for DEFAULT insert value in ON CONFLICT DO UPDATE query

Hi Is there any way to tell if a conflicting row in an multi-line INSERT used the DEFAULT directive? I would like to be able to upsert a bunch of rows and only UPDATE the conflicting rows where the value set was not new - the way I do this for NULLable columns is to just write NULL in the INSERT