Understanding Data Types

A column in a table is not just given a name; its definition also specifies what kind of data it's allowed to hold. This specification is known as the column's data type.

Think of data types as rules for your columns. Just like you wouldn't store soup in a sieve, you wouldn't store text like "Hello World" in a column meant only for numbers. Defining data types is fundamental because it ensures:

1. Data Integrity: It prevents you from accidentally inserting the wrong kind of data (e.g., text into a date column).
2. Storage Efficiency: The database system knows how much space to allocate for each value based on its type.
3. Correct Operations: It allows the database to perform meaningful operations (e.g., you can sum numbers, but you can't sum text).

While the exact names and features can vary slightly between different database systems (like PostgreSQL, MySQL, SQL Server, SQLite), the core concepts and common types are largely consistent. Here are the main categories you'll encounter frequently:

Numeric Types

These types handle numerical data.

• INTEGER (or INT): Used for whole numbers (numbers without decimal points). Examples include user IDs (like 101, 102), quantities (5, 100), or counts. They can be positive or negative. Variations like SMALLINT, TINYINT, BIGINT exist to store smaller or larger ranges of integers, optimizing storage space.
• DECIMAL (or NUMERIC): Used for numbers where exact precision is important, especially for currency or financial calculations. You typically specify the total number of digits (precision) and the number of digits after the decimal point (scale). For example, DECIMAL(10, 2) can store a number up to 10 digits long, with 2 digits reserved for the fractional part (e.g., 12345678.99).
• FLOAT, REAL, DOUBLE PRECISION: These are used for approximate-value numbers, often for scientific calculations where exact precision isn't as critical as handling a very wide range of values, including very small or very large numbers. They use a floating-point representation, which can sometimes lead to small rounding differences.

String (Character) Types

These types store text data.

• VARCHAR(n): Stands for variable character. This is one of the most common text types. It stores strings of varying lengths, up to a maximum length specified by n. For example, VARCHAR(255) can store any text string from 0 up to 255 characters long. The database only uses the storage needed for the actual characters stored, plus a small overhead. Use this when the length of the text varies significantly, like for names, addresses, or descriptions.
• CHAR(n): Stands for fixed character. This type stores strings of a fixed length, specified by n. If the string you insert is shorter than n, the database typically pads it with spaces to reach the exact length n. If the string is longer, it might be truncated or cause an error, depending on the database system. CHAR can be slightly faster for retrieval in some cases if the data length is always the same (e.g., storing two-letter country codes like US, CA, GB using CHAR(2)).
• TEXT: Used for storing long-form text data, like articles, comments, or detailed descriptions, where the maximum length might exceed what VARCHAR typically supports comfortably. The exact length limits for TEXT vary significantly between database systems but are generally very large.

Date and Time Types

These types are specifically designed to store temporal information.

• DATE: Stores only the date (year, month, and day). Example: 2023-10-27.
• TIME: Stores only the time of day (hours, minutes, and seconds, sometimes with fractional seconds). Example: 14:35:10.
• TIMESTAMP (or DATETIME): Stores both date and time information together. Example: 2023-10-27 14:35:10.123. These types are often used for logging events, like recording when an order was placed or a record was last updated. Some systems store timestamp information relative to UTC (Coordinated Universal Time), which helps manage time zones.

Boolean Type

• BOOLEAN: Represents logical TRUE or FALSE values. Some database systems don't have a specific BOOLEAN type and might use a small integer type instead (like TINYINT(1), where 0 represents FALSE and 1 represents TRUE). Used for flags or status indicators, like is_active, has_paid, or email_verified.

Visualizing Data Types in a Table Structure

Let's visualize how these types might be applied in a simple Customers table.

A diagram showing columns in a Customers table and their corresponding SQL data types. customer_id is typically an INTEGER and serves as a unique identifier (Primary Key). Names and email use VARCHAR for variable-length text. signup_date uses DATE. total_spent uses DECIMAL for precise currency values, and is_premium uses BOOLEAN for a true/false status.

Choosing the appropriate data type when designing a table is an important step. It ensures your data is stored correctly and efficiently, making it easier and more reliable to query and analyze later using SQL. As we move forward, you'll see how these types influence the way we write our queries, especially when filtering and performing calculations.