You can try the same behavior using the MERGE statement:
Example, creation of the target table:
CREATE TABLE target
(
id SERIAL PRIMARY KEY CONSTRAINT pk_tst,
name CHAR(1),
age SMALLINT
);
Create a temporary source table and insert the record you want:
CREATE TEMP TABLE source
(
id INT,
name CHAR(1),
age SMALLINT
) WITH NO LOG;
INSERT INTO source (id, name, age) VALUES (1, 'A', 19);
The MERGE would be:
MERGE INTO target AS t
USING source AS s ON t.id = s.id
WHEN MATCHED THEN
UPDATE
SET t.name = s.name, t.age = s.age
WHEN NOT MATCHED THEN
INSERT (id, name, age)
VALUES (s.id, s.name, s.age);
You'll see that the record was inserted then you can:
UPDATE source
SET age = 20
WHERE id = 1;
And test the MERGE again.
Another way to do it is create a stored procedure, basically you will do the INSERT
statement and check the SQL error code, if it's -100
you go for the UPDATE
.
Something like:
CREATE PROCEDURE sp_insrt_target(v_id INT, v_name CHAR(1), v_age SMALLINT)
ON EXCEPTION IN (-100)
UPDATE target
SET name = v_name, age = v_age
WHERE id = v_id;
END EXCEPTION
INSERT INTO target VALUES (v_id, v_name, v_age);
END PROCEDURE;