Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Insert/Update Upsert Trigger in Postgres
CREATE OR REPLACE FUNCTION upsert_user()
RETURNS trigger AS
$upsert_user$
declare
existing record;
begin
if (select EXISTS(select 1 from users where user_id = NEW.user_id)) then
select user_name, user_class, user_age into strict existing from users where user_id = new.user_id;
--found; update, and return null to prevent insert
UPDATE users SET
user_class = coalesce(new.user_class, existing.user_class),
user_name = coalesce(new.user_name, existing.user_name),
user_age = coalesce(new.user_age, existing.user_age),
modified = current_timestamp
WHERE user_id = new.user_id;
return null;
end if;
new.modified = current_timestamp;
return new;
end
$upsert_user$
LANGUAGE plpgsql;
create trigger users_insert
before insert
on users
for each row
execute procedure upsert_user();
@wmhilton
Copy link
Author

wmhilton commented Nov 9, 2015

Note to self: add a boilerplate method to psql-tools to create a upsert trigger.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment