Обсуждение: 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,
company_name TEXT NOT NULL,
person_name TEXT NOT NULL
)
INHERITS ( _audit_table, _delete_table );
and the following constraints
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);
In this example a record with a company name of 'widgets' already exists and will
result in an constraint violation
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
What would be the proper format for the "on conflict" clause ?
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 <blkimelman@gmail.com> wrote:
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,
company_name TEXT NOT NULL,
person_name TEXT NOT NULL
)
INHERITS ( _audit_table, _delete_table );
and the following constraints
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);
In this example a record with a company name of 'widgets' already exists and will
result in an constraint violation
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
What would be the proper format for the "on conflict" clause ?
On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman <blkimelman@gmail.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)
David J.
On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman <blkimelman@gmail.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 messageWhich 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)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
On Fri, Sep 23, 2022 at 4:42 PM Steve Baldwin <steve.baldwin@gmail.com> 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 Kimelman <blkimelman@gmail.com> wrote:
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,
company_name TEXT NOT NULL,
person_name TEXT NOT NULL
)
INHERITS ( _audit_table, _delete_table );
and the following constraints
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);
In this example a record with a company name of 'widgets' already exists and will
result in an constraint violation
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
What would be the proper format for the "on conflict" clause ?
Thanks for the response. But when I "prefixed" the columns with "excluded" I got a different error message
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
> 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 Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus <xof@thebuild.com> wrote:
> 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 need include 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 Sat, Sep 24, 2022 at 10:44 AM Christophe Pettus <xof@thebuild.com> wrote:
> 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 contrived example:
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 Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus <xof@thebuild.com> wrote:
> 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 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
> 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 Sat, Sep 24, 2022 at 8:49 AM 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
Nope, the words "DO UPDATE" mean "DO an UPDATE command instead of failing for the CONFLICTing INSERT command". As mentioned, if you want to ensure you perform an insert of a new record the data you are inserting must not conflict with existing data. You must do that prior to executing the command.
Or, in the words of the documentation:
The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.
...
ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.
Which seems sufficiently clearly written.
David J.
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