1. Defining boolean and temporal data columns
In the final lesson of the chapter, we will explore boolean and temporal data and their representations in PostgreSQL.
2. Boolean and temporal data
Recall the book table introduced in lesson 2 of this chapter.
This table could be altered such that some additional data is associated with each record. For example, the book publisher might also be interested in tracking the original publication date and whether or not the book is out of print.
3. Boolean and temporal data
"originally underscore published" is an example of temporal data while "out underscore of underscore print" is an example of boolean valued data.
4. The BOOLEAN data type
Boolean values can be used to represent the state of an object having one of three possible values.
One possible value is a true state.
The other possible value is a false state.
A third possibility for a boolean column's value is a null entry that is used if the value is unknown.
Boolean values are common for representing yes or no scenarios.
BOOL and BOOLEAN are interchangeable for specifying a boolean column. Definitions of boolean columns without the specification of a default value will default to the null value. However, the default value can be explicitly specified.
For instance, a retailer might want to default to having a product considered in stock when it is added to the products table using a definition of the "in underscore stock" column as shown here.
5. Temporal data types
Temporal values are used when representing a date and/or a time related to a table record.
The most complete temporal data type is the TIMESTAMP type which stores both a date and time as the column value. This data type might be used to represent the
starting date and time for a marketing promotion. For the representation of some temporal data only the date is required and no specific time is recorded.
The DATE type can be specified for a column in such circumstances. This would likely be the case when representing employees birthdates.
Equivalently, some temporal data only requires storage of a time value. A scenario for using this data type might arise when using a database table to represent when an automated report should be generated each day. While other format options exist for each of these temporal data types, the formats shown here represent the most common formats.
6. Let's practice!
This completes our overview of data types in PostgreSQL. Here we focused on the most common types for your data representation needs. Its time to practice what we have learned.