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:
- Data structures (array and JSON)
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 data types represent numbers. The following figure provides an overview of some of the main types:
Character data types store text information. The following figure summarizes 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
Booleans are a data type used to represent
False. The following table summarizes values that are represented as Boolean when used in a query with a data column type of Boolean:
While all these values are accepted, the values of
False are compliant with best practices. Booleans can also take on
datetime data type is used to store time-based information, such as dates and times. The following are some examples of
datetime data types:
You will explore this data type further in Chapter 7, Analytics Using Complex Data Types.