Postgresql Increment if exist or Create a new row

Question

Hello I have a simple table like that:

+------------+------------+----------------------+----------------+
|id (serial) | date(date) | customer_fk(integer) | value(integer) |
+------------+------------+----------------------+----------------+

I want to use every row like a daily accumulator, if a customer value arrives and if doesn't exist a record for that customer and date, then create a new row for that customer and date, but if exist only increment the value.

I don't know how implement something like that, I only know how increment a value using SET, but more logic is required here. Thanks in advance.
I'm using version 9.4

Answer 1

It sounds like what you are wanting to do is an UPSERT.

http://www.postgresql.org/docs/devel/static/sql-insert.html

In this type of query, you update the record if it exists or you create a new one if it does not. The key in your table would consist of customer_fk and date.

This would be a normal insert, but with ON CONFLICT DO UPDATE SET value = value + 1.

NOTE: This only works as of Postgres 9.5. It is not possible in previous versions. For versions prior to 9.1, the only solution is two steps. For 9.1 or later, a CTE may be used as well.

For earlier versions of Postgres, you will need to perform an UPDATE first with customer_fk and date in the WHERE clause. From there, check to see if the number of affected rows is 0. If it is, then do the INSERT. The only problem with this is there is a chance of a race condition if this operation happens twice at nearly the same time (common in a web environment) since the INSERT has a chance of failing for one of them and your count will always have a chance of being slightly off.

If you are using Postgres 9.1 or above, you can use an updatable CTE as cleverly pointed out here: Insert, on duplicate update in PostgreSQL? This solution is less likely to result in a race condition since it's executed in one step.

WITH new_values (date::date, customer_fk::integer, value::integer) AS (
  VALUES 
     (today, 24, 1)
),
upsert AS ( 
    UPDATE mytable m 
        SET value = value + 1
    FROM new_values nv
    WHERE m.date = nv.date AND m.customer_fk = nv.customer_fk
    RETURNING m.*
)
INSERT INTO mytable (date, customer_fk, value)
SELECT date, customer_fk, value
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.date = new_values.date
                  AND up.customer_fk = new_values.customer_fk)

This contains two CTE tables. One contains the data you are inserting (new_values) and the other contains the results of an UPDATE query using those values (upsert). The last part uses these two tables to check if the records in new_values are not present in upsert, which would mean the UPDATE failed, and performs an INSERT to create the record instead.

---

As a side note, if you were doing this in another SQL engine that conforms to the standard, you would use a MERGE query instead. [ https://en.wikipedia.org/wiki/Merge_(SQL) ]