Hello.
I was just asked by a mysql-user how do you do
insert ..... on duplicate key update ....
(or however they have it in mysql) in postgresql, if you are going to
run commands from the command line?
My solution up till now has been a function with the
BEGIN
insert ....
EXCEPTION WHEN OTHERS THEN
update ...
END;
I think this is not possible to run this kind of commands on the
command line. Correct?
As I see it, there are three ways
1) a function
2) try to rewrite it as two separate queries insert ... ; update
... ; where the insert will fail sometimes
3) try to search and see if there were any result back, but that would
require the IF THEN construct which is also not available outside of
functions, right?
Is it correct to assume that a function that is searching for the key
and then choosing to insert or update depending on what it found, is
about as fast as doing an insert within a begin- exception-end
statement or are there some inherent speed differences?
Comparing method 1 and 2, are there any noticable speed differences
when the exception is triggered?
What should I answer the mysql-user?
By the way, is there any work done on getting this functionality? I
must admit that it would be handy some times ;-)
Shouldn't this kind of question be added to the FAQ?