Make MERGE on PostgreSQL 9.5

Question

I'm trying to do a MERGE in PostgreSQL 9.5 and I get the following error:

ERROR:  syntax error at or near "MERGE"
LINE 1: MERGE INTO TP_ESTADO_EQUIPOS AS EQ
        ^

********** Error **********

ERROR: syntax error at or near "MERGE"
SQL state: 42601
Character: 1

SQL

MERGE INTO TP_ESTADO_EQUIPOS AS EQ
USING (SELECT * FROM TEMP_TABLE_STATE_EQUIPMENT) AS VEQ
ON EQ.ESTADO_EQUIPOS_ID = VEQ.ESTADO_EQUIPO_ID
 WHEN MATCHED THEN (
 EQ.TIEMPO_INICIO=VEQ.TIEMPO_INICIO,
 EQ.TIEMPO_FIN=VEQ.TIEMPO_FIN,
 ...
 )
WHEN NOT MATCHED THEN
INSERT(Estado_Equipos_ID,
        Tiempo_Inicio,
        ...
        )
        VALUES(VEQ.ESTADO_EQUIPO_ID,
                VEQ.Tiempo_Inicio,
                ...);

I have been reading documentation, and I may have to use UPSERT, but it is still not clear to me if that is necessarily the error.

@a_horse_with_no_name Question is not duplicate, i was trying to do a MERGE, but i didn't know that i had to use an UPSERT, so that was my question. — Levi Arista, Mar 20 '18 at 13:24
I think part of the problem here is that Google's first result for `postgres merge` is https://www.postgresql.org/message-id/attachment/23520/sql-merge.html, which describes a capability that has not actually been added. I wish they would - MSSQL's implementation is a lot better. — Stephen, Jan 31 '20 at 21:17

score 12 · Accepted Answer · edited Oct 02 '19 at 03:42

12

Postgres has no MERGE statement:

https://www.postgresql.org/docs/current/static/sql-commands.html

Use INSERT ON CONFLICT DO instead:

https://www.postgresql.org/docs/current/static/sql-insert.html

There are some movement towards MERGER statement, but it will not necessarily be smth that you expect:

https://wiki.postgresql.org/wiki/SQL_MERGE

edited Oct 02 '19 at 03:42

Levi Arista

295
1
3
13

answered Mar 20 '18 at 09:28

Vao Tsun

47,234
13
100
132

Yes, it is true until PostgreSQL 11 merge is not supported. – Indrajeet Gour Jul 27 '21 at 13:54
It looks like PostgreSQL 15 finally has the merge command: https://www.postgresql.org/docs/15/sql-merge.html – Anssssss Aug 18 '22 at 14:37

Make MERGE on PostgreSQL 9.5

1 Answers1

Linked