Question 199840 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL ON CONFLICT multiple arguments behavior is AND or OR

In PostgreSQL, the ON CONFLICT we are using can have multiple column names. Is that multiple columns behave like AND or OR?

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

Does this mean, conflict occurs in

name AND mobile

or

name or mobile
Tags:

2

Answers


  1. 0

    According to the documentation you need SELECT/UPDATE privilege to the columns specified.

    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.

    As long as either of the columns specified is in conflict_target it will perform conflict_action.

    where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
    Login or Signup to reply.
  2. 0

    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 ('name1','mobil1','addr1')
         , ('name2','mobil2','addr2');
        
select * 
  from test_on_conflict;
 
 
--- 2. insert same name,mobile without constraint   
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. 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 ('name1','mobil1','address1:update1') 
        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 ('name4','mobil4','address4') 
        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 ('name3','mobil1','address3') 
        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 ('name2','mobil3','address4') 
        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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search