I'm so used to MySQL that switching to other RDBMSs is a bit of a pain!

MySQL implements a query structure that allows one to attempt to insert a record, however if that INSERT were to insert a row with a duplicate key, it should update the original record in some manner. This is known as the "INSERT ... ON DUPLICATE KEY UPDATE ..." query.

I'm required to use Postgres for an assignment, and I'm trying to accomplish the same thing. After hours of research, I've finally come across Rules.

Here is my table:


CREATE TABLE user_stocks_table (
user_id varchar(20),
stock_symbol char(3),
amount integer
);


Here is the rule:


CREATE RULE insert_on_duplicate_update_user_stocks_table AS
ON insert TO user_stocks_table
WHERE (new.user_id, new.stock_symbol) IN (
SELECT user_id, stock_symbol
FROM user_stocks_table
WHERE user_id=new.user_id AND stock_symbol=new.stock_symbol)
DO INSTEAD
UPDATE user_stocks_table
SET amount = amount + new.amount
WHERE user_id=new.user_id AND stock_symbol=new.stock_symbol;


Now for some reason, whenever I insert a record that doesn't exist yet, it doubles the amount. But once that record exists, subsequent inserts update the amount correctly.

I've read the documentation, and it said that the INSERT will normally be carried out before whatever is specified in the DO. This would explain the doubling. However, the documentation further said that the INSERT would suppressed if it is DO INSTEAD, as opposed to DO ALSO.

Taken from: http://www.postgresql.org/docs/7.4/interactive/rules-update.html


For ON INSERT rules, the original query (if not suppressed by INSTEAD) is done before any actions added by rules. This allows the actions to see the inserted row(s).


Does "suppressed by INSTEAD" mean that the original query will not be executed? Or does it mean it will be executed after the INSTEAD? It would make more sense for it not to be executed...

So my question is, why does this rule double the amount on the very first insert? And how do I fix this?

Thanks,
Sadiq.