Integer

Right, let’s get our hands dirty with the actual doing of math. This isn’t high school algebra; it’s Python, which means it’s both incredibly powerful and occasionally, well, quirky. I’m going to walk you through the operators you’ll use every day and the built-in functions that’ll save your bacon. Pay attention—this is where the tiny, invisible gotchas live that can derail an entire analysis. The Usual Suspects: +, -, *, / These work exactly as you’d expect. Mostly. Addition, subtraction, multiplication—no surprises. Division (/) is where Python wised up. Even if you feed it two integers, it always returns a float. This is a fantastic design choice that prevents a whole class of “why is my result 4 instead of 4.5?” headaches that plague other languages.

Alright, let’s talk about the part of floating-point that feels like it was designed by someone who’d had a very long day and just started making up rules. We’ve got infinities, we’ve got Not-a-Number, and we’ve got comparisons that will make you question your own sanity. It’s not a bug; it’s a feature. A weird, infuriating, but ultimately logical feature. The Concept of NaN and Infinity First, let’s get this straight: Infinity isn’t just a theoretical concept; it’s a literal value you can assign to a variable. This happens when you do something that mathematically explodes towards infinity, like dividing a positive number by zero. And before you yell “that’s undefined!”, remember: we’re not in the realm of pure math anymore. We’re in IEEE 754 land, and here, we have rules.

Right, let’s talk about floating-point numbers. You’ve probably heard the horror stories: “never use floats for money,” “0.1 + 0.2 != 0.3,” and so on. It’s not that they’re evil; they’re just deeply, fundamentally misunderstood. They’re a tool, and like any powerful tool, you can shoot your own foot off spectacularly if you don’t know how it works. I’m here to make sure you keep all your toes. The root of all this chaos is a brilliant, clever, and occasionally infuriating standard called IEEE 754. Think of it as a committee’s best attempt to represent the infinite set of real numbers in a finite amount of memory. It’s a compromise, and like all compromises, it makes nobody perfectly happy but is better than the alternatives (like everyone inventing their own, even worse, formats).

Right, let’s talk about numbers that don’t lie. You’ve probably bumped into the weirdness of floating-point arithmetic already—the infamous 0.1 + 0.2 != 0.3 nonsense. That’s because float and double are for speed and approximation, not for accounting. They’re for when you’re simulating a galaxy and a picometer here or there doesn’t matter. When you’re counting money, or doing anything that requires exact decimal representation, you need a different tool. You need decimal.

Alright, let’s talk about serial and bigserial. You’ve probably seen these types all over the place, and you might have even used them as your go-to “auto-incrementing primary key” type. Here’s the first thing you need to know: they don’t actually exist. Wait, what? Don’t panic. Let me explain. serial and bigserial are not genuine data types in the way integer or text are. They are what we call a “shorthand” or “convenience notation”—a bit of syntactic sugar Postgres provides to make your life easier, which is a rare and beautiful thing in a database system. Under the hood, they are just a regular integer type (integer for serial, bigint for bigserial) with a sequence and a default value slapped on. It’s a facade, but a incredibly useful one.

Alright, let’s talk about the integer family: smallint, integer, and bigint. These are your workhorses, the bedrock of counting things. They’re not fancy, but understanding their limits is the difference between a robust application and a digital dumpster fire. The designers of PostgreSQL basically gave us three sizes: small, medium, and “you’ll probably run out of disk space first.” The core difference between them is simple: how many bytes they hog and, consequently, how high (or low) they can count. This isn’t just academic; picking the wrong one is like using a teacup to bail out a sinking ship—it’s a fundamentally flawed strategy that will end in tears.