what's the best way to emulate "insert ignore" and "on duplicate key update" with postgresql ?
views:
3614answers:
2
+1
A:
Looks like PostgreSQL supports a schema object called a rule.
http://www.postgresql.org/docs/current/static/rules-update.html
You could create a rule ON INSERT for a given table, making it do NOTHING if a row exists with the given primary key value, or else making it do an UPDATE instead of the INSERT if a row exists with the given primary key value.
I haven't tried this myself, so I can't speak from experience or offer an example.
Bill Karwin
2009-06-17 21:36:53
if i understood well these rules are triggers that get executed every time a statement is called. what if i want to apply the rule for only one query ? i have to create the rule then immediately destroy it ? (what about race conditions ?)
gpilotino
2009-06-17 22:21:56
Yes, I'd have the same questions as well. The rule mechanism is the closest thing I could find in PostgreSQL to MySQL's INSERT IGNORE or ON DUPLICATE KEY UPDATE. If we google for "postgresql on duplicate key update" you find other folks recommending the Rule mechanism, even though a Rule would apply to any INSERT, not just on an ad hoc basis.
Bill Karwin
2009-06-17 23:41:32
+4
A:
Try to do an UPDATE. If it doesn't modify any row that means it didn't exist, so do an insert. Obviously, you do this inside a transaction.
You can of course wrap this in a function if you don't want to put the extra code on the client side. You also need a loop for the very rare race condition in that thinking.
There's an example of this in the documentation: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html, example 38-1 right at the bottom.
That's usually the easiest way. You can do some margic with rules, but it's likely going to be a lot messier. I'd recommend the wrap-in-function approach over that any day.
This works for single row, or few row, values. If you're dealing with large amounts of rows for example from a subquery, you're best of splitting it into two queries, one for INSERT and one for UPDATE (as an appropriate join/subselect of course - no need to write your main filter twice)
Magnus Hagander
2009-06-18 07:00:44
"If you're dealing with large amounts of rows" that's exactly my case. I want to bulk update/insert rows and with mysql i can do this with only ONE query without any looping. Now I wonder if this is possible with postgresql too: to use just one query to bulk update OR insert. You say: "you're best of splitting it into two queries, one for INSERT and one for UPDATE" but how can I do an insert which does not throw errors on duplicate keys ? (ie. "INSERT IGNORE")
gpilotino
2009-06-18 09:41:56
Magnus meant that you use a query like this: "start transaction; create temporary table temporary_table as select * from test where false; copy temporary_table from 'data_file.csv'; lock table test; update test set data=temporary_table.data from temporary_table where test.id=temporary_table.id; insert into test select * from temporary_table where id not in (select id from test) as a"
Tometzky
2009-06-18 11:32:08