CAST

Every column in a table in a database has an assigned data type either by default or as assigned by the user. Sometimes when we query our data, we may wish to convert the data into a format so that we can perform operations only possible in that format.

For example, transforming a DATE format into STRING and vice versa gives you the flexibility to use functions specific to that data type. Some available/possible formats in the SQL language include:

• INTEGER
• DECIMAL
• DOUBLE/FLOAT
• BOOLEAN
• DATETIME
• STRING/VARCHAR

USING CAST

The CAST method syntax takes the following form CAST(column as DATATYPE) where column is a table column and DATATYPE is the intended conversion datatype.

In the example below, I demonstrate how to convert the price column which is float, to an integer and a string.

SELECT price, 
    cast(price as int) AS price_int, 
    cast(price as VARCHAR) as price_var 
from products limit 5;

The results will be similar with the exception of INT conversion which will strip away integer from prices

  price  | price_int | price_var
---------+-----------+-----------
  799.99 |       800 | 799.99
 2499.99 |      2500 | 2499.99
 1299.99 |      1300 | 1299.99
  599.99 |       600 | 599.99
  249.99 |       250 | 249.99
(5 rows)

The INTEGER casting truncates the total into an integer by rounding down the decimal values. This is useful to know as it may impact your calculations/analysis. Other data formats will also perform relevant truncation depending on their default setting.

This concludes our discussion on CAST method. In the next section, we look at the case method for managing multiple if cases.