Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Make MERGE on PostgreSQL 9.5 [duplicate]

Tags:

sql

postgresql

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.

like image 576
Levi Arista Avatar asked Mar 19 '18 16:03

Levi Arista


People also ask

Can we use MERGE in PostgreSQL?

PostgreSQL UsageCurrently, PostgreSQL version 10 doesn't support the use of the MERGE command. As an alternative, consider using the INSERT… ​ ON CONFLICT clause, which can handle cases where insert clauses might cause a conflict, and then redirect the operation as an update.

Is upsert same as MERGE?

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.

Is there an upsert in Postgres?

PostgreSQL lets you either add or modify a record within a table depending on whether the record already exists. This is commonly known as an "upsert" operation (a portmanteau of "insert" and "update").


1 Answers

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

like image 86
Vao Tsun Avatar answered Sep 22 '22 01:09

Vao Tsun