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_front
totrue
when theviolation_in_front_of_or_opposite
value is equal to'F'
for the record. - Include another case condition that sets the value of
is_violation_in_front
tofalse
when theviolation_in_front_of_or_opposite
value 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;