Data type conversions

1. Data type conversions

Welcome back to the course! In this chapter, we will be focusing on converting data between formats to provide the best match between data values and data types provided by PostgreSQL.

2. Type conversion (an example)

As a reminder, the score column of the restaurant_inspection table is a sum of the points earned due to violations encountered during an inspection. Imagine that we want to perform an analysis to determine the average score for initial inspections in our dataset. The simplest way to import the restaurant_inspection dataset into a database table is to represent every column as a TEXT type. However, it is not possible to calculate an average value using the TEXT representation of numbers. Fortunately, we are not bound by the type used in a table column. We can convert from one type to another in PostgreSQL.

3. Determining column types

Before going on to consider how to convert from one type to another, knowing how to determine a column's type is useful. In much of this course, we consider data imported into a table as a TEXT type. However, we often need to perform conversions between non-TEXT data types. The data types of the columns in the restaurant_inspection table can be displayed using the query displayed here. This query outputs records with the name of each column and corresponding data type.

4. Determining column types

Alternatively, if the goal is simply to determine the data type of a single column, we can add a condition limiting the column_name values returned by the query as demonstrated here. The result in this case will return a single record corresponding to the camis column. The information_schema schema and columns table in the FROM clause are automatically created and maintained with each PostgreSQL installation. You can simply query them to get the needed data type information as demonstrated in these examples.

5. Conversion with CASE

A few different options exist for performing type conversions. One option is to use a CASE clause. Imagine wanting to calculate a grade point average for each boro to get a sense of how well restaurants located in each boro are adhering to food safety rules. In this scenario, an A grade gets 3 points, a B receives 2 points, and a C earns 1 point. The boro grade point average can be calculated using the query displayed here. This query converts the values in the grade column to a corresponding value in the grade_point column. The resulting grade_point value is used as the argument to the aggregation function AVG() to get the desired value for each boro.

6. Conversion with CASE

The resulting grade point averages for each boro are displayed here. This example shows us how the CASE clause can be useful for performing type conversions. However, this usage has limitations due to the need to specify a conversion value for each possible grade. When there are only 3 grades, this is a reasonable approach. However, imagine if there are 20 or even 100 possible values for a column. This approach becomes tedious and unmanageable very quickly.

7. Conversion with CAST

Fortunately, many type conversions can be performed automatically. Consider a case where we desire to calculate the difference between the highest and lowest score received by a restaurant. Possible results are displayed here. Your first attempt to write a query to generate these results might look like the one shown here. However, this query would not work as written because the score column is represented as a TEXT value.

8. Conversion with CAST()

PostgreSQL provides the CAST operator to convert the score to a value of a different type. Using the CAST() operator, the query displayed previously can be re-written to get the desired results.

9. Conversion with double colon (::)

The CAST() operator is a SQL standard and is available in SQL implementations other than PostgreSQL. However, PostgreSQL provides a short-hand for type conversion using a double-colon syntax. This shorthand requires fewer keystrokes to convert a value from one type to another. The double-colon syntax can replace CAST() in the previous query resulting in the query displayed here.

10. Let's practice!

Now that you have seen different approaches to converting values of one type to another, let's practice what you have learned.

Create Your Free Account

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