Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson
Become a member
to unlock all features

Level Up!

Access all courses & lessons on egghead today and lock-in your price for life.


    Create a History Table in Postgres with Joins

    Brett CassetteBrett Cassette

    Possibly my favorite data management trick is the history table. With history tables, we record all changes in two locations -- once in the primary table (as an update), and once in a history table (as an insert). With this handy trick, we save an audited record of all data, when it was live, and who changed it and when. Coupled with non-destructive deletes, you can explore the complete history of your data with joins.



    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson




    Let's take a look at a real-world use case for joins. We can select off from a bids table. This bid table is backed by a list of histories. These are audits for the table. We can get to those audits if we inter-join on the bid histories. On the bids.ID equals the bid histories.bidID.

    We can also find out what user placed that bid on u.ID equals bid histories.history userID. We can order by a field that this will have column histories started at. We can take a look at what this looks like. It's a little hard to see, so let's start breaking it down.

    Let's say we want to get the bid's object name -- this is what was bidded on -- the user's name. We want to get the bid histories sense. How much do they bid? We want to get their history started at, history ended at. That should be enough for us.

    We can see here that the first thing that was bid on was this poster, First Dog bid of five dollars. We can see at what time he bid that, and what time it ended. Then Skitter bid seven dollars, what that time bid started and ended.

    We can continue to see the history of how this bidding war played out, ultimately resulting in these two values that we saw in the previous table. We see that they have null ended at columns, indicating that they're the present bid.

    This is how a table like this tends to work. Under the hood, there would be some backing structure that says in order for this transaction to succeed, we'll have to insert a record into the history table as well. This is a really good use case for joins, is being able to look at an audit trail and figure out exactly when your data changed, and who changed that data.