Database Reference
In-Depth Information
Setting and resetting counter columns
In certain situations, we might want to set a counter column directly; for instance, if we are
backfilling our
daily_status_update_views
table from historical information in
status_update_views
, one approach would be to calculate the view counts for each
day in our application, and then write the counts directly to
daily_status_update_views
.
The natural way to do this would be to issue an
INSERT
statement to put the known total
view count in the row for a given day:
INSERT INTO "daily_status_update_views"
("year", "date", "total_views")
VALUES (2014, '2014-02-01', 500);
However, if we try to perform this operation, we'll see an error:
As it turns out, the
only
way to mutate a counter column value is to increment or decrement
it; the value cannot be set directly. For the backfill use case, our best bet is just to issue an
increment statement for each of the raw views we have stored. If we know that the
daily_status_update_views
table has no information stored for the day in ques-
tion prior to the backfill, we can also simply perform a single increment, since adding our
computed aggregate to the current value of zero will effectively insert the computed ag-
gregate:
UPDATE "daily_status_update_views"
SET "total_views" = "total_views" + 500
WHERE "year" = 2014
AND "date" = '2014-02-01';
On checking our daily aggregates table again, we'll see our upsert has had the intended ef-
fect:
