Demo: SQL "UPDATE ... FROM" for upsert/merge operations. Reflecting Meltano's PostgreSQL data loader (target) adapter.

update-from-merges.sql

-- Demo: SQL "UPDATE ... FROM" for upsert/merge operations.
-- Reflecting Meltano's PostgreSQL data loader (target) adapter.
--
-- Usage:
--
--   psql postgresql://postgres@localhost:5432/ < update-from-merges.sql
--   crash --host http://crate@localhost:4200/ < update-from-merges.sql

-- Make a blank slate.
DROP TABLE IF EXISTS main;
DROP TABLE IF EXISTS updates;

-- Main table and table for updates have the same structure.
CREATE TABLE main    (name TEXT, temperature FLOAT, humidity FLOAT);
CREATE TABLE updates (name TEXT, temperature FLOAT, humidity FLOAT);

-- A few samples for the main table.
INSERT INTO main (name, temperature) VALUES ('POINT1', 42.42);
INSERT INTO main (name, temperature) VALUES ('POINT2', 43.43);
INSERT INTO main (name, temperature) VALUES ('POINT3', 0);

-- A few records to be updated.
INSERT INTO updates (name, temperature) VALUES ('POINT3', 44.44);
INSERT INTO updates (name, temperature) VALUES ('FOO', 55.55);
INSERT INTO updates (name, temperature, humidity) VALUES ('POINT1', 42.4255, 84.84);

-- Merge upsert data from one table to another.
-- See `target_postgres.sinks.PostgresSink.upsert` vs. `target_cratedb.sinks.CrateDBSink.upsert`.
UPDATE main SET
    temperature=updates.temperature,
    humidity=updates.humidity
FROM
    updates
WHERE
    main.name=updates.name;

-- Only on CrateDB.
-- REFRESH TABLE main;

-- It should contain the records `('POINT3', 44.44)` and `('POINT1', 42.4255, 84.84)`
-- from the upsert table.
SELECT * FROM main ORDER BY name;


-- Expected result:
--
--   name  | temperature | humidity
-- --------+-------------+----------
--  POINT1 |     42.4255 |    84.84
--  POINT2 |       43.43 |
--  POINT3 |       44.44 |