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

`"more logic is required here"` I'm afraid that more information is also required. Are you using a language along with Postgresql or did you have in mind to use a procedure? — Tim Biegeleisen, Jun 11 '15 at 01:37
In order to get my requirement is needed apply more logic, I don't know if is possible do something like that with a procedure. — Goku, Jun 11 '15 at 01:39
You don't need the id field. I just changed my answer. Your primary key should be the date and customer_fk to trigger the ON CONFLICT resolution part of the insert syntax. — Centijo, Jun 11 '15 at 01:53
@centijo Could be the date field the primary key? because that field will not be unique.. — Goku, Jun 11 '15 at 02:16
@Goku You need a composite primary key. The primary key would be made up of the date and customer_fk. That's what makes it unique. Take a look at this: http://stackoverflow.com/questions/1285967/postgres-how-to-do-composite-keys — Centijo, Jun 11 '15 at 02:17
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/80237/discussion-between-centijo-and-goku). — Centijo, Jun 11 '15 at 02:48

score 14 · Accepted Answer · edited May 23 '17 at 11:58

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) ]

PostgreSQL has (the equivalent of) `UPSERT` now? Cool! Any idea when this arrived? — j_random_hacker, Jun 11 '15 at 01:53
It's in 9.5-dev. There's no UPSERT/MERGE in stable versions of Postgres. How long have we been complaining about this? — Centijo, Jun 11 '15 at 01:55
If you could use anything else, this would be easy. But then, you probably wouldn't be asking on SO either. ;) — Centijo, Jun 11 '15 at 02:38

Postgresql Increment if exist or Create a new row

1 Answers1