Introduction to PostgreSQL data types

1. Introduction to PostgreSQL data types

Welcome back! In the last chapter, we focused on high-level commands for creating a database including the CREATE DATABASE, CREATE SCHEMA, and CREATE TABLE commands. In this chapter, we will further explore the variety of types of data that can be stored in our databases and how to determine which representations of data are best in different scenarios.

2. Data categories in PostgreSQL

There are a number of different data categories available in PostgreSQL. These include Text data for storing items such as names and product descriptions Numeric data for storing items such as order size or physical measurements Temporal data for storing items such as delivery dates or timestamps And boolean data for storing items that have true/false values There are other data categories as well but we will be focusing on the types described above

3. Example 1: representing birthdays

Imagine you are a school principal and would like to acknowledge the birthday's of your school's students. One of the students (let's call her Cathy) was born on May 3rd, 2006. We have a few options for representing Cathy's birthday in the database. These include Spelling out the month and writing the date and year separated with a comma using a textual representation Alternatively, we could use numbers for the month, day, and year separated by forward-slashes stored as text Or we could use the date representation utilized by PostgreSQL which places the year first, followed by a numeric representation for the month, and finally utilizing a day with a dash separating the values. These are all valid representations of a date. The last one has the benefit of being standardized so that a fixed length value can represent any date. Also, dates can be searched using ranges by using such a format.

4. Example 2: tracking payment status

Imagine being the organizer of a conference for a marketing professionals organization. You would like to track if members have paid the conference fee. Some possible representations include Storing a yes or no status using a text column Storing a Y representing yes and an N representing no using a text column Storing true for yes and false for no using a boolean column. Notice the flexibility that is provided by choosing a text representation for this data. Often, flexibility is good. However, in this case, a benefit of choosing to represent payment status as a boolean value is the restriction placed on what will be accepted as valid input. A text column, however, will allow input of values that cannot be easily interpreted as a payment status. For example, how would you handle an input value of the letter 'D' for a member's payment status? This is valid input for a text column but not representative of valid data for a payment status.

5. Example 3: trip distances

A consulting company wants to track the mileage that consultants are traveling. One of the consultants, Mark, recently flew 326 miles to meet a client. This data can be represented in a number of ways Using a text column, the unit of measurement can be directly included in the stored value The value could be stored in a text column without a unit Or the value could be represented in its most natural format as a numeric column. The numeric representation is best here because it easily enables summation operations that might be of interest such as summing mileage or finding trips that exceeded a certain limit.

6. Let's practice!

I hope the examples that you have seen help to illustrate the importance of choosing the best data type to represent different data values in a PostgreSQL database. Let's practice what you have learned during this lesson!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.