Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    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:
    Code:
    CREATE TABLE user_stocks_table (
     user_id varchar(20),
     stock_symbol char(3),
     amount integer
    );
    Here is the rule:
    Code:
    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/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).
    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.
    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


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •