Chapter_03_Practical SQL: Understanding Data Types.

Chapter 3: Understanding Data Types — Detailed Summary Introduction Chapter 3 focuses on one of the most fundamental concepts in SQL database design: data types. The chapter opens by emphasizing how important it is to understand the data type assigned to each column in a table. The author mentions the value of a data dictionary — a document that describes each column, its type, and its purpose — but acknowledges that many organizations don't maintain one, making it necessary to inspect table structures directly in tools like pgAdmin. The core message is simple but critical: storing data in the appropriate format is essential for building usable databases and performing accurate analysis. Each column in a SQL table can hold one and only one data type, declared at the time the table is created using the CREATE TABLE statement. The chapter organizes data types into three major categories: Characters — text and symbols Numbers — whole numbers and decimals Dates and Times — temporal data Character Data Types Character types are general-purpose and can store any combination of text, numbers, and symbols. PostgreSQL supports three main character types: char(n) — Fixed-Length This type stores a fixed number of characters defined by n. If the inserted string is shorter than n, PostgreSQL automatically pads the remaining space with blank spaces. For example, inserting "abc" into a char(10) column results in "abc " (with 7 trailing spaces). This type is largely a legacy format from older computer systems and is rarely used today, though some developers still use it for columns that will always contain the same number of characters, such as two-letter U.S. state abbreviations (char(2)). varchar(n) — Variable-Length with Limit This stores up to a maximum number of characters defined by n, but unlike char, it does not pad unused space. If you insert "abc" into a varchar(10) column, only 3 characters are stored. This makes it more storage-efficient than char and is one of the most commonly used character types in practice. text — Unlimited Variable-Length This type stores strings of any length, theoretically up to about 1 gigabyte. It is not part of the official SQL standard but is supported by PostgreSQL, Microsoft SQL Server, and MySQL. It offers maximum flexibility and is useful when you cannot predict how long a string might be. Key Takeaway on Characters According to PostgreSQL documentation, there is no significant performance difference among the three types. However, varchar and text are generally preferred because they avoid unnecessary storage use. A practical strategy is to use varchar with a generous n value to handle edge cases, while reserving text for truly unpredictable lengths. Number Data Types Number columns store numeric values and — importantly — allow mathematical operations to be performed on them. This distinguishes them from numbers stored as character strings, which cannot be added, multiplied, or divided. The chapter divides number types into integers and decimal numbers. Integers Integers are whole numbers, both positive and negative, including zero. SQL provides three integer types differing in storage size and range: TypeStorageRangesmallint2 bytes−32,768 to +32,767integer4 bytes−2,147,483,648 to +2,147,483,647bigint8 bytes−9.2 quintillion to +9.2 quintillion The chapter advises using bigint as a safe default unless you are certain the values will fit within the constraints of integer or smallint. For columns like days of the month or years, smallint is a sensible choice because values are always small and predictable, saving storage space. If you attempt to insert a number outside the allowed range of any integer type, PostgreSQL will return an out of range error and halt the operation. Auto-Incrementing Integers (Serial Types) The chapter revisits the serial types introduced in Chapter 1: small serial, serial, and bigserial. These are not true independent data types but rather special implementations of their corresponding integer types that automatically increment each time a new row is inserted, starting from 1. They are commonly used to create unique ID numbers (primary keys) for each row in a table, allowing tables to be linked together in a relational database. The chapter notes that gaps can appear in serial sequences if rows are deleted or if an insert is aborted. Decimal Numbers — Fixed-Point Fixed-point numbers are defined using numeric(precision, scale) or equivalently decimal(precision, scale). Here, precision is the total number of digits allowed across both sides of the decimal point, and scale is the number of digits allowed to the right of the decimal point. For example, numeric(5,2) allows up to 5 total digits with exactly 2 after the decimal, such as 121.50 or 1.00. If fewer decimal digits are provided, PostgreSQL pads with zeros. If more are provided, it rounds. This type is ideal for situations requiring exact precision, such as financial calculations involving