merge in Postgres - using insert on conflict

Question

I am trying to perform upsert using merge operator , soon enough realised postgres does not have same merge statement like SQL Server, and multiple posts are referring to "insert on conflict" statement instead to simulate.

trying to simulate below in postgresSQL - can you please help, as I dont see any examples :

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

I have below datasets - Can you please point how to do this in postgres using "insert on conflict" or any other way;

TabA
==============
id   |  Val
------------
1    | 20     
3    | 30          
5    | 30 

TabB

id   |  Val
------------
3    | 40          
6    | 50

baxx · Accepted Answer · 2023-07-11 19:09:46Z

2

MERGE command is now available in Postresql 15.

MERGE INTO table [ [ AS ] alias ]
USING source-query
ON join_condition
[when_clause [...]]

where when_clause is

{ WHEN MATCHED [ AND condition ] THEN { merge_update | DELETE }
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

where merge_update is

UPDATE SET { column = { expression | DEFAULT } |
           ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_insert is

INSERT [( column [, ...] )] { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

edited Jul 11, 2023 at 19:09

baxx

4,95414 gold badges57 silver badges129 bronze badges

answered Oct 19, 2022 at 19:22

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

skan Over a year ago

When should I use MERGE and when INSERT ON CONFLICT?

Igor Borisenko Over a year ago

@skan if you just insert and update table then they are the same you can use whatever you like. But if you need also to DELETE than MERGE shoulbe the choise.

Belayer · Accepted Answer · 2020-05-13 17:09:03Z

Postgres does not support Merge. It does have a somewhat limited on conflict. It is limited to bypassing the statement or updating specified columns. Further it does not have a independent select that merge using provided, but this can be simulated by a CTE.
You do not a description of what you want to do and do not describe the usage of TabA or TabB, nor what is the desirded. e . Please provide task description, actual table definitions and actual results desired form the sample data.
Without specific references I cannot give a substantial query, but but the format would seem to be:

with CTE as (select column_list from source_table)  
insert into target_table(column_list)
  select column_list from CTE
  on conflict on ... 
  do update
       set column_name = excluded.column_name
         , column_name = excluded.column_name , ...;

I don't know what the "WHEN NOT MATCHED BY SOURCE" does in Sql Server. It does appear it may be the same as Oracle's "merge ... when matched delete where" construct. Unfortunately Postgres does not provide corresponding capability.

Collectives™ on Stack Overflow

merge in Postgres - using insert on conflict

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related