# Upsert query using CTE in PostgreSQL 9.4

I have a generic pipeline in SnapLogic that logs the start time of the executions of a group of incremental jobs. Suppose, the log table is as follows.

CREATE TABLE sl_control.interface_parameters_values (
interface_id varchar NOT NULL,
parameter_name varchar NOT NULL,
parameter_value varchar NULL
);


A few points to remember:

• There may or may not be an entry for a particular job (interface)
• This generic pipeline only updates the IncrementalTimestamp parameter of the interface. So, there will be only one entry to insert/update per interface.
• The individual values (for example, 'TEST0001', 'IncrementalTimestamp', 0) are being generated by a script and are being passed in batches of 150 to the query. So, VALUES part of the query will have at most 150 entries for each execution.
• Although I'm setting the batch as 150, the average number of interfaces I'm expecting is ~30.

Following is my upsert query:

WITH
/* write the new values */
v (
interface_id,
parameter_name,
parameter_value
) AS (
VALUES ('TEST0001', 'IncrementalTimestamp', 0),
('TEST0002', 'IncrementalTimestamp', 0),
('TEST0003', 'IncrementalTimestamp', 0),
('TEST0004', 'IncrementalTimestamp', 0),
('TEST0005', 'IncrementalTimestamp', 0)
),
/* update existing rows */
upsert AS (
UPDATE sl_control.interface_parameters_values ipv
SET
interface_id    = v.interface_id,
parameter_name  = v.parameter_name,
parameter_value = v.parameter_value
FROM v
WHERE (
ipv.interface_id   = v.interface_id
AND ipv.parameter_name = v.parameter_name
)
RETURNING ipv.interface_id
)
/* insert missing rows */
INSERT INTO sl_control.interface_parameters_values (
interface_id,
parameter_name,
parameter_value
)
SELECT interface_id
, parameter_name
, parameter_value
FROM v
WHERE v.interface_id NOT IN (
SELECT interface_id FROM upsert
)


I had to take this approach because this particular PostgreSQL instance is version 9.4 and it does not support the ON CONFLICT syntax.

This query is working fine but is there any way this can be optimized further? Or is there any unseen pitfall to this approach. If this approach is good then I'll use this in one of our integration pipelines.

• Looks good to me. – 404 Dec 1 '18 at 13:48