Right, so you want to build a system where things need unique IDs across different tables. Maybe you’re stitching together events from a user action log and an admin audit log into a single timeline. Or perhaps you’re building a distributed system and you need to guarantee that an ID generated in your orders table in one database will never, ever clash with an ID generated in your invoices table in another.
Alright, let’s pull back the curtain on one of PostgreSQL’s most seductively “clever” features: table inheritance. It feels like you’ve just been handed a superpower. You can create a master table and have child tables that automatically inherit its columns. Need to model different types of vehicles? A base vehicles table with children cars, trucks, and boats seems so elegant. But here’s the brutal truth, straight from the trenches: for the use case you’re probably thinking of—partitioning your data for performance—native inheritance is often a trap dressed up as a solution. It’s the coding equivalent of a beautifully designed sports car with a lawnmower engine under the hood.
Alright, let’s talk about table inheritance. This is one of those PostgreSQL features that sounds like an absolute dream on the whiteboard and can turn into a bit of a nightmare in production if you’re not careful. I’m not here to scare you off—it’s a powerful tool—but I am here to make sure you understand its quirks so you don’t end up cursing my name at 3 AM. The core idea is simple: you can have a parent table and child tables that inherit its structure. This is classic object-oriented “is-a” thinking, bolted directly onto a relational database. It’s perfect for modeling situations where you have a central concept (like a “vehicle”) with specific subtypes (“car,” “truck,” “motorcycle”) that share common attributes but also have their own special columns.
Right, let’s talk about IDENTITY columns. You’ve probably used SERIAL before—PostgreSQL’s old-school, convenience-wrapper way to make an auto-incrementing column. It’s fine. It works. But under the hood, it’s just a sequence plopped onto a column with a default value. The SQL standard has a more explicit, more powerful, and frankly, less janky way to do this: the IDENTITY column. It’s the grown-up version, and it’s what you should be using for new tables unless you have a very specific reason not to.
Right, let’s talk about the four functions that let you peer under the hood of your sequences and, more importantly, let you get yourself into a world of trouble if you’re not careful. You’ve created a sequence, you’re using nextval() in your INSERTs, and everything seems like magic. But sometimes you need to break the illusion, to ask the sequence, “Hey, what number are you actually on?” or, in a moment of hubris, tell it what number to be on. That’s what these functions are for.
Alright, let’s talk about sequences. Think of a sequence as a number dispenser. You walk up to it, press the big red button, and out pops a number, one higher than the last. It’s a simple, brilliant, and utterly essential concept for generating unique identifiers. The SERIAL pseudo-type you might have used is just PostgreSQL being friendly and wrapping this whole process into a one-liner. But when you use CREATE SEQUENCE directly, you get the keys to the machine. You can tweak its gears, and with that power comes the responsibility not to shoot yourself in the foot.