MERGE in PostgreSQL 9.5

Question

MERGE in PostgreSQL 9.5

Navigation

#1 by (3 votes)

1

I am 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. Help please.

postgresql

asked by Levi Arista 19.03.2018 в 15:43

source

1 answer

Get selected option within an optgroup Validate reservation entry and exit range. Bootstrap datepicker

score 3 · Accepted Answer

The UPSERT consists of making a common insert, but including an ON CONFLICT to indicate the field or fields that can not be repeated and then a DO UPDATE SET to assign the columns with the new values that will be assigned if a record already exists with the same field (s) indicated in the ON CONFLICT.

INSERT INTO TP_ESTADO_EQUIPOS 
(SELECT * FROM TEMP_TABLE_STATE_EQUIPMENT) 
 ON CONFLICT (estado_equipos_id)
 DO UPDATE SET 
 TIEMPO_INICIO=EXCLUDED.TIEMPO_INICIO,
 ... ;