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.
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.
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.
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").
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With