Numeric

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.

Alright, let’s talk about making your database actually useful. You’ve got your standard-issue data types: INTEGER, TEXT, VARCHAR. They’re the raw lumber. Powerful, but if you build a house with just planks and nails, you’ll end up with doors that are seven feet tall and windows that let in the rain. This is where domain types come in. Think of them as custom molds or jigs. They let you take a base type and slap on constraints and a more meaningful name, ensuring the data that goes in actually makes sense for your specific problem.

Right, so you’ve mastered the basic scalar types – integers, text, booleans, the usual suspects. They’re the solo artists. But data is rarely about lone values; it’s about relationships. A person has a first name and a last name. A point on a graph has an x-coordinate and a y-coordinate. Enter the Row Type, PostgreSQL’s brilliantly simple way to keep these related values bundled together. Think of it as a single-column table, a structured record you can pass around as a single unit. It’s the database’s way of saying, “These things belong together, so let’s treat them that way.”

Right, so you’ve decided to get serious about PostgreSQL’s Write-Ahead Log (WAL). Good for you. It’s the secret sauce behind almost every “how does Postgres do that?!” feature, from crash recovery to replication. And to understand the WAL, you need to understand its address system: the Log Sequence Number, or pg_lsn. This isn’t your average integer; it’s a data type specifically designed to talk about points in the transaction log, and it’s far more elegant than just throwing a BIGINT at the problem.

Right, let’s talk about UUIDs. You’ve probably seen these 36-character monstrosities (a0eebc99-9c0b-4d8b-b654-9b1f7d4e8b66) lurking in your database. They look like someone fell asleep on their keyboard, but I promise there’s a method to the hexadecimal madness. We use them when we need a unique identifier that can be generated anywhere—on your client, in your API, in ten different microservices at once—without anyone having to call a central “id-issuing authority” (like a database sequence) and risk a bottleneck or a collision.

Right, let’s talk about the various ways PostgreSQL can remember where things live and how to talk to them. We’re not just storing ‘www.example.com’ here; we’re dealing with the raw, structured numbers that actually make the network work. This is for when you need to know if an IP is within a range, validate a MAC address, or just store this stuff without losing your mind trying to do it in a string.

Alright, let’s talk about shapes. No, not the ones you failed to cut out in kindergarten. We’re dealing with Postgres’s geometric types, a wonderfully useful and occasionally maddening set of tools for when you need to store more than just numbers and text. They live in their own little coordinate-based universe, and while they’re powerful, they have a few quirks that will make you want to gently headbutt your monitor. I’m here to guide you through that.

Right, let’s talk about the building blocks. Before you can build anything clever, you need to know your bricks, mortar, and the occasional weird, custom-shaped brick that some previous architect decided was a good idea. In PostgreSQL, your data types are your bricks, and they fall into three main categories: Primitive, Composite, and Domain. Think of them as Lego bricks, pre-built Lego sets, and official Lego sets you’ve modified with a marker.