问题

多列的 ON CONFLICT 行为是按照 AND 的逻辑进行的。

ON CONFLICT (name, mobile) DO UPDATE SET
address = EXCLUDED.address;

name AND mobile

name or mobile

答案1

得分: 1

根据文档，您需要具备对指定列的SELECT/UPDATE权限。

类似地，当指定ON CONFLICT DO UPDATE时，您只需要对列进行UPDATE权限，这些列在要更新的列表中列出。但是，ON CONFLICT DO UPDATE还需要对在ON CONFLICT DO UPDATE表达式或条件中读取其值的任何列具有SELECT权限。

只要指定的列之一在conflict_target中，它就会执行conflict_action。

其中conflict_target可以是以下之一：

（{index_column_name |（index_expression）} [COLLATE collation] [opclass] [, ...]）

Similarly, when ON CONFLICT DO UPDATE is specified, you only need UPDATE privilege on the column(s) that are listed to be updated. However, ON CONFLICT DO UPDATE also requires SELECT privilege on any column whose values are read in the ON CONFLICT DO UPDATE expressions or condition.

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )

答案2

得分: 1

on conflict(col1,col2) 在列上建立了一个AND条件，所有列必须与现有行的值匹配。此外，您不能仅仅命名任何列，您必须首先在特定列上定义唯一约束。最后，在给定的DML语句中只能指定一个on conflict()条件。如下所示是示例：

-- 1. 设置
create table test_on_conflict(toc_id   integer generated always as identity
                                       primary key
                             ,name     text not null
                             ,mobile   text not null
                             ,address  text not null
                             ); 
                             
insert into test_on_conflict( name, mobile,address) 
    values ('name1','mobil1','addr1')
         , ('name2','mobil2','addr2');
        
select * 
  from test_on_conflict;
 
 
--- 2. 插入相同的name和mobile，没有约束条件   
insert into test_on_conflict( name, mobile, address) 
    values ('name1','mobil1','address1:update1') 
        on conflict (name, mobile)   
        do update
              set address= excluded.address;

select * 
  from test_on_conflict; 
         
-- 3. 因此，定义强制约束条件
alter table test_on_conflict 
      add constraint just_one_name_mobile
          unique(name, mobile); 
  
-- 4. 再次尝试
insert into test_on_conflict( name, mobile, address) 
    values ('name1','mobil1','address1:update1') 
        on conflict (name, mobile)   
        do update
              set address= excluded.address;

select * 
  from test_on_conflict; 
 

-- 5. 新name，新mobile
insert into test_on_conflict( name, mobile, address) 
    values ('name4','mobil4','address4') 
        on conflict (name, mobile)   
        do update
              set address= excluded.address;
             
select * 
  from test_on_conflict; 
 
-- 6. 新name，旧mobile
insert into test_on_conflict( name, mobile, address) 
    values ('name3','mobil1','address3') 
        on conflict (name, mobile)   
        do update
              set address= excluded.address;
             
select * 
  from test_on_conflict;
 
-- 7. 旧name，新mobile
insert into test_on_conflict( name, mobile, address) 
    values ('name2','mobil3','address4') 
        on conflict (name, mobile)   
        do update
              set address= excluded.address;
             
select * 
  from test_on_conflict;

 
请注意，*conflict_action* 仅在语句集＃4 中执行，其中两列都匹配。

<details>
<summary>英文:</summary>

An `on conflict(col1,col2)` establishes and AND condition on the columns, all must match values from an existing row. Further you cannot just name the any columns you must first define `unique constraint` on the specific columns. Finally only one `on conflict()` condition can be specified in a given DML statement. See demo as duplicated below:

    -- 1. setup
    create table test_on_conflict(toc_id   integer generated always as identity
                                           primary key
                                 ,name     text not null
                                 ,mobile   text not null
                                 ,address  text not null
                                 ); 
                                 
    insert into test_on_conflict( name, mobile,address) 
        values (&#39;name1&#39;,&#39;mobil1&#39;,&#39;addr1&#39;)
             , (&#39;name2&#39;,&#39;mobil2&#39;,&#39;addr2&#39;);
            
    select * 
      from test_on_conflict;
     
     
    --- 2. insert same name,mobile without constraint   
    insert into test_on_conflict( name, mobile, address) 
        values (&#39;name1&#39;,&#39;mobil1&#39;,&#39;address1:update1&#39;) 
            on conflict (name, mobile)   
            do update
                  set address= excluded.address;
    
    select * 
      from test_on_conflict; 
             
    -- 3. So define the constraint to enforce 
    alter table test_on_conflict 
          add constraint just_one_name_mobile
              unique(name, mobile); 
    
    -- 4. and try again
    insert into test_on_conflict( name, mobile, address) 
        values (&#39;name1&#39;,&#39;mobil1&#39;,&#39;address1:update1&#39;) 
            on conflict (name, mobile)   
            do update
                  set address= excluded.address;
    
    select * 
      from test_on_conflict; 
     
     
    -- 5. new name, new mobile
    insert into test_on_conflict( name, mobile, address) 
        values (&#39;name4&#39;,&#39;mobil4&#39;,&#39;address4&#39;) 
            on conflict (name, mobile)   
            do update
                  set address= excluded.address;
                 
    select * 
      from test_on_conflict; 
     
    -- 6. new name, old mobile
    insert into test_on_conflict( name, mobile, address) 
        values (&#39;name3&#39;,&#39;mobil1&#39;,&#39;address3&#39;) 
            on conflict (name, mobile)   
            do update
                  set address= excluded.address;
                 
    select * 
      from test_on_conflict;
     
    -- 7. old name, new mobile
    insert into test_on_conflict( name, mobile, address) 
        values (&#39;name2&#39;,&#39;mobil3&#39;,&#39;address4&#39;) 
            on conflict (name, mobile)   
            do update
                  set address= excluded.address;
                 
    select * 
      from test_on_conflict;

 
Notice that the *conflict_action* is executed only in statement set #4 where both columns match.

</details>