Got it, since PostgreSQL 9.4 doesn't support the ON CONFLICT clause (that feature landed in 9.5), you have a couple of solid workarounds to achieve the same upsert behavior without upgrading your database. Let's walk through them:

Method 1: Update First, Insert If No Rows Affected #

This approach uses a transaction with PL/pgSQL logic to first attempt an update, then insert only if the update didn't hit any rows.

BEGIN;

-- Attempt to update the existing row
UPDATE test_table
SET dt = current_timestamp, amt = 15
WHERE id = 4;

-- If no rows were updated (meaning the ID doesn't exist), insert a new row
IF NOT FOUND THEN
    INSERT INTO test_table (id, dt, amt)
    VALUES (4, current_timestamp, 15);
END IF;

COMMIT;

Notes: #

• You can wrap this logic in a reusable PL/pgSQL function if you need to run this upsert frequently.
• Always run this in a transaction to avoid race conditions between the update and insert steps.

Method 2: Use a CTE to Combine Update and Insert #

If you prefer pure SQL (no PL/pgSQL), you can use a Common Table Expression (CTE) to capture the updated rows, then insert only if no updates occurred.

WITH updated_rows AS (
    UPDATE test_table
    SET dt = current_timestamp, amt = 15
    WHERE id = 4
    RETURNING *
)
INSERT INTO test_table (id, dt, amt)
SELECT 4, current_timestamp, 15
WHERE NOT EXISTS (SELECT 1 FROM updated_rows);

Notes: #

• This works because the CTE runs first: if the update finds a matching row, updated_rows will contain that row, and the WHERE NOT EXISTS clause skips the insert. If no row was updated, the insert proceeds.
• Like the first method, execute this within a transaction for safety.

Critical Pre-Requisite & Concurrency Tips #

• Unique Constraint: Make sure the id column has a primary key or unique index. Without this, both methods can result in duplicate rows (just like ON CONFLICT relies on a unique constraint to detect conflicts).
• Concurrency Handling: In high-concurrency environments, you might still hit a unique_violation error if two sessions attempt to insert the same id at the same time. To handle this:
  • Use the SERIALIZABLE transaction isolation level (it prevents these race conditions but has a small performance cost).
  • Or, in your application code, catch the unique_violation exception and retry the upsert operation.

内容的提问来源于stack exchange，提问作者user3685285