Postgres 9.4.7 INSERT INTO without ON CONFLICT

Question

I would really like to write a query like this:

INSERT INTO test_table (id, dt, amt)
VALUES(4, current_timestamp, 15)
ON CONFLICT (id)
DO UPDATE
    SET dt = VALUES(dt)
        amt = VALUES(amt)

The problem is, the version of Postgres I'm using (9.4.7) doesn't support the ON CONFLICT construct. Any ideas of how to achieve this without upgrading Postgres?

Answer 1

PostgreSQL 9.4 doesn't have any built-in UPSERT (or MERGE) facility, and doing it efficiently in the face of concurrent use is very difficult.

This article discusses the problem in useful detail.

In general you must choose between two options:

• Individual insert/update operations in a retry loop; or
• Locking the table and doing batch merge

For complete solution read this SO Answer

Answer 2

If you do it frequently. You can write a function to do it for you. You can do by two ways.

1. Check first then insert or update.

    CREATE OR REPLACE FUNCTION insert_test_table(int, timestamp, varchar)
    RETURNS int AS
    $BODY$
    BEGIN
        IF (EXISTS(SELECT id FROM test_table WHERE id = $1)) THEN
            UPDATE test_table SET dt = $2, amt = $3 WHERE id = $1;
            RETURN 2;
        ELSE
            INSERT INTO test_table (id, dt, amt) VALUES($1, $2, $3);
            RETURN 1;
        END IF;
        RETURN 0;
        END;
    $BODY$
    LANGUAGE plpgsql;

2. Use Exception:

    CREATE OR REPLACE FUNCTION insert_test_table(int, timestamp, varchar)
    RETURNS int AS
    $BODY$
    BEGIN
        INSERT INTO test_table (id, dt, amt) VALUES($1, $2, $3);
        RETURN 1;
        EXCEPTION WHEN unique_violation THEN 
                BEGIN
                    UPDATE test_table SET dt = $2, amt = $3 WHERE id = $1;  
                    RETURN 2
                END;
    END;
    $BODY$
    LANGUAGE plpgsql;

The second way maybe conflict with other unique constraint, so be careful.