I am running on postgres 9.5

I have a table with events (in theory, it is, currently, an append only store).

Certain event types can appear only once on a given date. (So currently, we have a partial unique index and an upsert to update the "value" column when a conflict occurs <- so this is why it is not a fully append only store)

The goal: to be able to version the events mostly as an audit of past events - mostly in the sense that these events represent a misinterpretation of the facts and have thus been superseded by corrected events

I have, presently, come up with the notion by which I add a Nullable column with a timestamp to each event (I call it substituted_at). An event that has been superseded will have the timestamp recorded while the newest events will have that column value remain NULL. This should, hopefully, make retrieval of the events easy, for processing, I simply retrieve all events whose substituted_at value is NULL.

Another solution would have been to have moved the superseded events to an auxiliary table, but that didn't strike me as the way to go (comments?)

(Also, I am open to other propositions)

Sub goals:

  1. version events whose type can only appear once per date
  2. version events whose type can appear multiple times on a given date
  3. it should be possible to version an event without supplying a replacement event (in effect removing it from the event store for processing)


I would like to center on sub-goal #1.

It would seem that in case of conflict I need to update the substituted_at column with a timestamp and keep all the other values as they are. I would also need to insert a new row with the new data.

It does not seem possible with the ON CONFLICT Clause.

I would obviously like to do this transactionally.

Also the solution should keep in mind that sub-goal #2 cannot rely on an ON CONFLICT clause - since it is alright to have more than one event per type per date.

Also, sub-goal #3 means that there would be times where no replacement would come at all.

As I write these lines, it seems to me that centering on UPSERT to do the heavy lifting is not the robust way I should be heading... Also, in the face of versioning, does the partial unique index make sense anymore?

  • sorry for the long question
    – Yaneeve
    Aug 9, 2017 at 10:47
  • please feel free to edit to make it more to the point
    – Yaneeve
    Aug 9, 2017 at 10:47
  • yes I am a developer and not a dba
    – Yaneeve
    Aug 9, 2017 at 10:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.