Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
02-14-2005, 08:22 AM #1
- Join Date
- Feb 2004
- Thanked 0 Times in 0 Posts
[POSTGRESQL] Implementing INSERT ... ON DUPLICATE UPDATE ?
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 );
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;
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/i...es-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).
So my question is, why does this rule double the amount on the very first insert? And how do I fix this?
Last edited by sad69; 02-14-2005 at 08:29 AM. Reason: Changed URL. Same information, however I'm using Postgres 7.4, not 8.0