title: PostgreSQL increment values of rows in a table
body: <p>I'm currently PHP building a script that has to update statistics when it finishes it's purpose. The script is accessed by web browser and depending on the traffic it may be executed simultaneously. I have to guarantee the statistics are right.</p>
  
  <p>To give you the picture let's say we have a table:</p>
  
  <pre><code>CREATE TABLE statistics(
    user_id      integer NOT NULL,
    date         integer NOT NULL, -- for unix time
    stat1        integer NOT NULL DEFAULT 0,
    stat2        integer NOT NULL DEFAULT 0,
    stat3        integer NOT NULL DEFAULT 0  -- and so on...
  );
  
  -- Let's insert some testing data for a couple of users and days...
  -- Day one
  INSERT INTO statistics(1, 1303520820, 1, 1, 1);
  INSERT INTO statistics(2, 1303520820, 1, 1, 1);
  -- Day two
  INSERT INTO statistics(1, 1303603200, 1, 1, 1);
  INSERT INTO statistics(2, 1303603200, 1, 1, 1);
  -- Day three
  INSERT INTO statistics(1, 1303689600, 1, 1, 1);
  INSERT INTO statistics(2, 1303689600, 1, 1, 1);
  </code></pre>
  
  <p>Every day a new row is inserted in the table so we could have a daily, weekly, monthly, yearly statistics. I have to be sure only one row is inserted per <b>user_id</b> per day. Also whenever an UPDATE query is executed it would increment the columns <b>stat1</b>,<b>stat2</b>,<b>stat3</b> appropriately. </p>
  
  <p>This script is expected to have quite some traffic and I want to figure out how to make things work when the script is executed and has a couple of instances working simultaneously. What method/technique do you find best for such tasks ?</p>