Before we talk about all of the many data types that Postgres has, we need to do two things in this video. The first is we need to disambiguate some terms. Because if you're coming from a different database or this is your first experience with Postgres, you might be a little bit confused by some of the terms we're about to use. After that, I want to try to impress upon you why it is so important that we build out our tables correctly.

The first thing we need to do is talk about this word schema. In SQLite and MySQL, you just have a database that's full of tables, right? You have a database, it's full of tables and data and views and triggers and all of that kind of stuff, right? That is slightly different.

In Postgres, you have a database, and inside the database, you can have multiple schemas. If you're coming from certain programming backgrounds, you might think of that as a namespace. Inside of your database, you can have multiple schemas. Inside those schemas, that is where your tables and your data and all of that stuff lives.

Within Postgres, there's one more level of organization available to you, and we'll talk about some of this in other videos, but there's one more level of organization available to you. Much like a file system. You can think of a schema as a directory. You cannot have nested schemas. But loosely, when you open up a schema, you see all of your tables in there.

Now that's very, very different than SQLite and MySQL. If you think about it kind of on a spectrum from left to right. SQLite, you have a single file that has a database in it, and inside the database are tables and data and all that stuff. In MySQL, you've got your MySQL server that inside of it can have multiple databases, but inside those databases, you just have your tables and your data and your views and your triggers and all that stuff.

Postgres is very different. You have your Postgres server or your Postgres cluster, the Postgres process. Inside of that, you can have multiple databases. Each database can have multiple schemas. Eeach schema, obviously, has multiple tables. That's a lot. However, there's another commonly used or commonly understood way to talk about schema, and that is the structure and or definition of your table. When we talk about a user's table, we can say, "What's the schema of that table?" You kind of have to go off context clues. When you're saying, "What is the schema of that table?" You're talking about, "What is the structure? What are the columns? How are they defined? Do they have any check constraints?" Anything like that. If you're saying, "Well, what schema are you querying against?" Or, "What schema does that table live in?" That means something else. That is the Postgres concept of a schema. Hopefully that clears it up just a little bit.

Every Postgres database by default starts out with a public schema, which we will talk about, but you need to understand the difference between the way that Postgres organizes things and the way that some other databases organize things.

Now, the second thing I want to accomplish in this video is try to impress upon you the importance of building out your tables correctly. The guiding principles that we're gonna be moving forward in this module with are, we need to keep our table schemas small. We need to keep them as small as we possibly can, we need to keep them as simple as we possibly can, and we need to keep them representative of our data.

One of the great things about Postgres is it's many different data types. It's going to be incumbent upon you as the developer, the DBA, the manager, whatever your role is. It's going to be incumbent upon you to know the universe of possibilities. What are all the data types that Postgres offers? Then compare that with what is the shape of my data? What does my data look like? What are the bounds of my data? And then pick the smallest, simplest, most representative data type for your data. Now, hear me out. This is not a pursuit or an exercise in futility. We're not going to try to shave off bits and bytes, and in the process, mangle our data. Please don't mangle your data just so you can save a little space here and there. It is not worth it. However, look at your data, and figure out, "What are the bounds?" Can it only ever go from zero to 100? Great. Let's not pick a data type that goes up to two quintillion. Let's just pick a data type that covers zero to a hundred.

Again, this is not about saving a little bit of disc space here and there, but it is about, one, enforcing, potentially, some sort of domain logic. If this can only go from zero to 100, maybe we make it a small column, put a check constraint on it. Well, that's kind of nice. Maybe we make it a small simple column such that when we index it, that index is more efficient. That's also pretty nice.

If you have a number and you store it in a character type column, not only are you confusing all of your coworkers because that column is not representative of the truth, you're also making your life a lot harder because Postgres has ways of handling numeric columns, and you've just shoved a bunch of numbers into a character column. Now, your database isn't working as efficiently or as effectively as possible. And that goes for every data type.

If you have a UUID, let's use the UUID data type because Postgres is built to handle that data type. Lest you hear me saying, "Pick a small, compact representative data type to save a little disc space," please know that it's much broader, much bigger than that. This is our first opportunity as we're building out the schema, as we're building up our database. This is our first opportunity to do something really great. To do it right, to do it as right as we can. It's okay to change it in the future, but this is our first opportunity to get it right, which will set us up for future success as we move towards indexing and querying and performance, and all of that stuff.

Hopefully, I have impressed upon you that this is important, that knowing all of the data types is valuable, and that you can match your data. The bounds of your data and the shape of your data can be matched to one of the Postgres data types.