Basic Data Types of SQL
Each column in a table has a data type. You will explore the major data types of PostgreSQL here. These types include:
- Numeric
- Character
- Boolean
- Datetime
- Data structures (array and JSON)
Note
Although the ANSI SQL standard defines a list of data types, different RDBMSs may have their own interpretations and extensions. The data types discussed in this book are based on the PostgreSQL definition. If you use a different RDBMS, you may see some differences in implementation. Furthermore, all RDBMSs, including PostgreSQL, are actively evolving. They constantly add support for new data types, and slightly adjust data type implementations if necessary. So, it is always prudent to use the data type definitions in this book as general guidance and double-check your RDBMS for the exact data type definitions it has.
Numeric
Numeric data types represent numbers. The following figure provides an overview of some of the main types:
Figure 2.28: Major numeric data types
Character
Character data types store text information. The following figure summarizes character data types:
Figure 2.29: Major character data types
Under the hood, all character data types use the same underlying data structure in PostgreSQL (and in many other RDBMSs). The most common character data type is varchar(n).
Boolean
Booleans are a data type used to represent True or False. The following table summarizes values that are represented as Boolean when used in a query with a data column type of Boolean:
Figure 2.30: Accepted Boolean values
While all these values are accepted, the values of True and False are compliant with best practices. Booleans can also take on NULL values.
Datetime
The datetime data type is used to store time-based information, such as dates and times. The following are some examples of datetime data types:
Figure 2.31: Popular datetime data types
You will explore this data type further in Chapter 7, Analytics Using Complex Data Types.