Type conversion with a CASE clause
One of the parking_violation attributes included for each record is the vehicle's location with respect to the street address of the violation. An 'F' value in the violation_in_front_of_or_opposite column indicates the vehicle was in front of the recorded address. A 'O' value indicates the vehicle was on the opposite side of the street. The column uses the TEXT type to represent the column values. The same information could be captured using a BOOLEAN (true/false) value which uses less memory.
In this exercise, you will convert violation_in_front_of_or_opposite to a BOOLEAN column named is_violation_in_front using a CASE clause. This column is true for records that occur in front of the recorded address and false for records that occur opposite of the recorded address.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Exercise instructions
- Include one case condition that sets the value of
is_violation_in_fronttotruewhen theviolation_in_front_of_or_oppositevalue is equal to'F'for the record. - Include another case condition that sets the value of
is_violation_in_fronttofalsewhen theviolation_in_front_of_or_oppositevalue is equal to'O'for the record.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
CASE WHEN
-- Use true when column value is 'F'
___ = ___ THEN ___
WHEN
-- Use false when column value is 'O'
___ = ___ THEN ___
ELSE
NULL
END AS ___
FROM
parking_violation;