This post is a short story about a bug that we found a couple of days ago in our backend application. The bug that originated with a simple mistake in an SQL statement, but it was the one we'd expect to be handled by the database itself. Some time ago we considered ditching MySQL for Postgres, and as a matter of fact, that switch would have saved us this time. Let's see how both products handle something we can call "casted joins".
When saving data to an SQL database, we often use some kind of autogenerated primary keys for our entities. It's very handy since we don't need to keep track of it and make sure we don't have duplicate identifiers. On the other hand, when inserting data we are not sure how we can retrieve it, as it wasn't chosen by us. Actually, using PostgreSQL it is very easy to achieve, so let's take a look how we can do that in a Golang application.
We've taken a look at the ways to represent OOP inheritance in an SQL database: single table, multiple tables, and a reference table. We were using PostgreSQL for those examples, and this database allows us to use one more way of representing such a relation. Let's take a look on PostgreSQL's INHERITS.
When building our applications, we often try to identify some problem and transform it into a programming language by creating appropriate data structures and relations between them. Then, when we want to store that data in an SQL database, things sometimes get tricky. In this post, I want to show three ways we can represent real-life inheritance in an SQL database.