StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgreSQL increment values of rows in a table
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload