sqlpostgresqlupsert

INSERT rows that were missing for the UPDATE

I want to write a query that, if I was using variables, would look something like (pseudocode)

records = SELECT * FROM table_1 WHERE id = x

numberOfRowsUpdated = UPDATE table_2 SET column_1 = y WHERE id = record1.id

INSERT INTO table2 (columns) (records.id, records.name, ...) where records exists and numberOfRowsUpdated == 0

Currently, I have something like this, but the syntax does not seem valid. Would someone be able to provide any help on including an update statement in the WHERE clause?

WITH records AS (SELECT id, tx_id FROM table_1 WHERE id = NEW.id)
INSERT INTO table_2 (id,txn_id)
SELECT records.id, records.txn_id)
FROM records
WHERE EXISTS (SELECT 1 FROM records)
AND (UPDATE table_2 SET has_txn = true WHERE id in (records.id) = 0)

I got it working in a janky way, and I would like to improve it so I am not querying records multiple times

WITH records AS (SELECT * FROM table_1 WHERE txn_id = NEW.id),
numberOfRowsUpdated AS (
    UPDATE table_2 SET has_txn = true WHERE id in (SELECT id FROM 
    table_1 WHERE txn_id = NEW.id) RETURNING 1
)
INSERT INTO outbox (
 id,
 ...
) SELECT (
 records.id,
 ...
 )
FROM records, numberOfRowsUpdated
WHERE EXISTS (SELECT 1 FROM records)
AND (select count(*) from numberOfRowsUpdated) = 0;
Solution

  • Like Laurenz already suspected, looks like an UPSERT can do it:

    INSERT INTO balance_activity_outbox (id, ...)
SELECT id, ...
FROM   table_1
WHERE  txn_id = NEW.id
ON     CONFLICT (id) DO UPDATE
SET    has_txn = true
WHERE  has_txn IS NOT TRUE;

    Requires a unique constraint/index or primary key on balance_activity_outbox(id).

    I added a final WHERE has_txn IS NOT TRUE to cancel empty updates. Only useful if that can occur. See: