Find Postgres Data That Doesn’t Match Join Conditions with Full Outer Join

Find Lonely Postgres Data with Left and Right Join

Detect Duplicates in Postgres with Cartesian Product

Find Related Data with Inner Join in Postgres

See All Rows from Different Postgres Tables with Union All

Join Tables in Postgres with Complex Conditions

Create a History Table in Postgres with Joins

Use Joins to Detect Orphaned Data in Postgres

Understand Joins and Unions in Postgres

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.

https://app.egghead.io/api/v1/lessons/postgresql-create-a-history-table-in-postgres-with-joins/subtitles

[00:00] 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.

[00:30] 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.

[00:53] 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.

[01:16] 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.

[01:38] 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.

[01:53] 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.

Create a History Table in Postgres with Joins

Share this video with your friends

Course

Understand Joins and Unions in Postgres