This is the kind of lecture we could've started the whole course with. But then your first tasks and lectures would've been super boring. If you decided to try being a builder for a day, what would you want to start with: learning about types and shapes of bricks, or driving a tower crane for 30 minutes? Exactly :)
A student isn’t a vessel you need to fill, it’s a torch you need to light up (and not burn out)! I’m not trying to tell you everything I know, but to make it interesting for you. And right now is the perfect time to start getting to know data types.
A data type in PostgreSQL is basically a way to say what kind of info will be stored in a certain table column. For example, numbers, text, or dates. It’s kinda like picking a box for storage: a small one for tiny stuff, a big one for shoes, a clear one for threads so you can see what’s inside right away.
Example:
- If you want to store a person’s age, you’ll want a numeric data type.
- If you need to save a name, you’ll need a text data type.
- And if you want to mark the registration date, it makes sense to use a date data type.
Why do we need data types?
Data types help you:
- Limit what kind of info can be entered. For example, you can’t insert text where a number is expected.
- Structure your data. When you know exactly what’s in each column, it’s way easier to write queries and analyze stuff.
- Save resources. Using the right types means you use less memory.
- Keep things accurate. Like, financial calculations need fixed-point numbers.
Example to get inspired
Imagine a table for storing info about students:
id |
name |
age |
enrollment_date |
|---|---|---|---|
| 1 | Otto Mars | 22 | 2023-01-15 |
| 2 | Anna Song | 19 | 2023-02-10 |
Here, id is a unique identifier (a number), name is text, age is the age as a number, and enrollment_date is the student’s enrollment date. All these pieces of data have different data types because they describe different aspects of the object.
Classification of Data Types in PostgreSQL
PostgreSQL is insanely flexible when it comes to data types. Here’s a quick breakdown.
Main categories of data types:
Numeric types
- For storing whole numbers (like quantity, id) and numbers with a fractional part (like money).
- Examples:
INTEGER,NUMERIC,FLOAT.
Text types
- For storing texts and strings (like names, addresses).
- Examples:
CHAR,VARCHAR,TEXT.
Boolean type
- For storing logical values
TRUE(true) orFALSE(false). - Example:
BOOLEAN.
- For storing logical values
Types for working with dates and time
- For storing dates, time, or their combo.
- Examples:
DATE,TIME,TIMESTAMP.
Special types
- Unique identifiers, JSON objects, arrays, and a bunch more.
- Examples:
UUID,JSONB,ARRAY.
Features of Data Types in PostgreSQL
PostgreSQL supports a huge variety of built-in types and lets you create custom (user-defined) data types. This makes working with this DBMS especially powerful for complex systems where you need specific data requirements.
Fun fact: in PostgreSQL you can even store geographic data or run queries like "Find a cafe within 500 meters of me." For stuff like that, there are special data types.
What does explicitly specifying data types give you?
Sometimes it seems like it’d be easier to just make all the columns in a table text—since text can store anything. But that’s a bad idea! Specifying the right data types lets you:
Make queries simpler. If PostgreSQL knows there are only numbers in a column, it can do calculations like SUM() way faster.
Avoid mistakes. Imagine you accidentally saved "thirty two" instead of 32 in a column where ages are stored. Catching mistakes like that would be super hard.
GO TO FULL VERSION