Upsert with PostgreSQL

Since version 9.5 PostgreSQL provides support for upsert. Upsert allows you to "atomically either insert a row, or on the basis of the row already existing, UPDATE that existing row instead, while safely giving little to no further thought to concurrency". While experimenting with the Prooph Event Store library I came across a good use-case for upsert, so I gave it a try. The goal was to build a projection based on the event stream to count the number certain events based for a specific day - think of registrations per day.

The database table for the projection looks like this:

CREATE TABLE reg_projection
(
    reg_date DATE DEFAULT now() PRIMARY KEY,
    reg_count INT DEFAULT 0
);

I was looking for a way to either insert a new record if no record for the date in question existed or update the number of the events. After a bit of research I came up with this SQL statement:

INSERT INTO "user_reg_projection" ("reg_date", "reg_count") VALUES ('2017-01-02', 1) ON CONFLICT ("reg_date") DO UPDATE SET "reg_count" = "reg_count" + 1 WHERE "reg_date" = '2017-01-02';

Unfortunately PostgreSQL responded with the following error message for the query:

[42702] ERROR: column reference "reg_count" is ambiguous

It took me quite a while to figure out what the problem was. By accident I came across this question on stackoverflow which made things clear:
In the update part of the query you need to tell PostgreSQL to either use the value stored in the the database row - this is actually what I was looking for - or to use the value that should be inserted. To distinguish both cases you either need to prefix the fields with the name of the database table to indicate that the actual value from the database row should be used or you need to prefix the fields with "EXCLUDED" to indicate PostgreSQL should use the value that should get inserted. Since I simply want to increase the counter that is already stored in the database row, all I needed to do was prefix all the fields used in the update part of the query with the name of the database table:

INSERT INTO "user_reg_projection" ("reg_date", "reg_count") VALUES ('2017-01-02', 1) ON CONFLICT ("reg_date") DO UPDATE SET "reg_count" = "user_reg_projection"."reg_count" + 1 WHERE "user_reg_projection"."reg_date" = '2017-01-02';