Postgres Upsert (insert Or Update) Only If Value Is Different
Solution 1:
Take a look at a BEFORE UPDATE trigger to check and set the correct values:
CREATEOR REPLACE FUNCTION my_trigger() RETURNSTRIGGERLANGUAGE plpgsql AS
$$
BEGIN
IF OLD.content = NEW.content THEN
NEW.updated_time= OLD.updated_time; -- use the old value, not a new one.ELSE
NEW.updated_time= NOW();
END IF;
RETURNNEW;
END;
$$;
Now you don't even have to mention the field updated_time in your UPDATE query, it will be handled by the trigger.
http://www.postgresql.org/docs/current/interactive/plpgsql-trigger.html
Solution 2:
Two things here. Firstly depending on activity levels in your database you may hit a race condition between checking for a record and inserting it where another process may create that record in the interim. The manual contains an example of how to do this link example
To avoid doing an update there is the suppress_redundant_updates_trigger() procedure. To use this as you wish you wold have to have two before update triggers the first will call the suppress_redundant_updates_trigger() to abort the update if no change made and the second to set the timestamp and username if the update is made. Triggers are fired in alphabetical order. Doing this would also mean changing the code in the example above to try the insert first before the update.
Example of how suppress update works:
DROPTABLE sru_test;
CREATETABLE sru_test(id integernotnullprimary key,
data text,
updated timestamp(3));
CREATETRIGGER z_min_update
BEFORE UPDATEON sru_test
FOREACHROWEXECUTEPROCEDURE suppress_redundant_updates_trigger();
DROPFUNCTION set_updated();
CREATEFUNCTION set_updated()
RETURNSTRIGGERAS $$
DECLAREBEGIN
NEW.updated := now();
RETURNNEW;
END;
$$ LANGUAGE plpgsql;
CREATETRIGGER zz_set_updated
BEFORE INSERTORUPDATEON sru_test
FOREACHROWEXECUTEPROCEDURE set_updated();
insertinto sru_test(id,data) VALUES (1,'Data 1');
insertinto sru_test(id,data) VALUES (2,'Data 2');
select*from sru_test;
update sru_test set data ='NEW';
select*from sru_test;
update sru_test set data ='NEW';
select*from sru_test;
update sru_test set data ='ALTERED'where id =1;
select*from sru_test;
update sru_test set data ='NEW'where id =2;
select*from sru_test;
Solution 3:
Postgres is getting UPSERT support . It is currently in the tree since 8 May 2015 (commit):
This feature is often referred to as upsert.
This is implemented using a new infrastructure called "speculative insertion". It is an optimistic variant of regular insertion that first does a pre-check for existing tuples and then attempts an insert. If a violating tuple was inserted concurrently, the speculatively inserted tuple is deleted and a new attempt is made. If the pre-check finds a matching tuple the alternative DO NOTHING or DO UPDATE action is taken. If the insertion succeeds without detecting a conflict, the tuple is deemed inserted.
A snapshot is available for download. It has not yet made a release.
Solution 4:
INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;
https://www.postgresqltutorial.com/postgresql-upsert/
Dummy example :
insert intouser_profile (user_id, resident_card_no, last_name) values
(103, '14514367', 'joe_inserted')
on conflict on constraint user_profile_pk do
update set resident_card_no = '14514367', last_name = 'joe_updated';
Solution 5:
The RETURNING clause enables you to chain your queries; the second query uses the results from the first. (in this case to avoid re-touching the same rows) (RETURNING is available since postgres 8.4)
Shown here embedded in a a function, but it works for plain SQL, too
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATETABLE my_table
( updated_time timestampNOTNULLDEFAULT now()
, updated_username varcharDEFAULT'_none_'
, criteria1 varcharNOTNULL
, criteria2 varcharNOTNULL
, value1 varchar
, value2 varchar
, PRIMARY KEY (criteria1,criteria2)
);
INSERTINTO my_table (criteria1,criteria2,value1,value2)
SELECT'C1_'|| gs::text
, 'C2_'|| gs::text
, 'V1_'|| gs::text
, 'V2_'|| gs::text
FROM generate_series(1,10) gs
;
SELECT*FROM my_table ;
CREATEfunction funky(_criteria1 text,_criteria2 text, _newvalue1 text, _newvalue2 text)
RETURNS VOID
AS $funk$
WITH ins AS (
INSERTINTO my_table(criteria1, criteria2, value1, value2, updated_username)
SELECT $1, $2, $3, $4, COALESCE(current_user, 'evgeny' )
WHERENOTEXISTS (
SELECT*FROM my_table nx
WHERE nx.criteria1 = $1AND nx.criteria2 = $2
)
RETURNING criteria1 AS criteria1, criteria2 AS criteria2
)
UPDATE my_table upd
SET value1 = $3, value2 = $4
, updated_time = now()
, updated_username =COALESCE(current_user, 'evgeny')
WHERE1=1AND criteria1 = $1AND criteria2 = $2-- key-conditionAND (value1 <> $3OR value2 <> $4 ) -- row must have changedANDNOTEXISTS (
SELECT*FROM ins -- the result from the INSERTWHERE ins.criteria1 = upd.criteria1
AND ins.criteria2 = upd.criteria2
)
;
$funk$ languagesql
;
SELECT funky('AA', 'BB' , 'CC', 'DD' ); -- INSERTSELECT funky('C1_3', 'C2_3' , 'V1_3', 'V2_3' ); -- (null) UPDATE SELECT funky('C1_7', 'C2_7' , 'V1_7', 'V2_7777' ); -- (real) UPDATE SELECT*FROM my_table ;
RESULT:
updated_time|updated_username|criteria1|criteria2|value1|value2----------------------------+------------------+-----------+-----------+--------+---------2013-03-13 16:37:55.405267|_none_|C1_1|C2_1|V1_1|V2_12013-03-13 16:37:55.405267|_none_|C1_2|C2_2|V1_2|V2_22013-03-13 16:37:55.405267|_none_|C1_3|C2_3|V1_3|V2_32013-03-13 16:37:55.405267|_none_|C1_4|C2_4|V1_4|V2_42013-03-13 16:37:55.405267|_none_|C1_5|C2_5|V1_5|V2_52013-03-13 16:37:55.405267|_none_|C1_6|C2_6|V1_6|V2_62013-03-13 16:37:55.405267|_none_|C1_8|C2_8|V1_8|V2_82013-03-13 16:37:55.405267|_none_|C1_9|C2_9|V1_9|V2_92013-03-13 16:37:55.405267|_none_|C1_10|C2_10|V1_10|V2_102013-03-13 16:37:55.463651|postgres|AA|BB|CC|DD2013-03-13 16:37:55.472783|postgres|C1_7|C2_7|V1_7|V2_7777(11rows)
Post a Comment for "Postgres Upsert (insert Or Update) Only If Value Is Different"