Redshift Cast Functions 101: Syntax and Queries Simplified

Organizations accompanying traditional data warehouses not only witness storage limitations but also struggle to process the rising rate of data. However, Amazon Redshift provides a fast, reliable, and cloud-based data warehouse solution that eliminates scaling issues and assists analysts to gain key insights with business intelligence tools. As Redshift utilizes SQL at the backend, it also helps convert data types using the Redshift CAST function for simplifying data manipulation in diverse forms based on the requirements.

This article gives an overview of the Amazon Redshift CAST function. It introduces Redshift and provides a glimpse of SQL commands and data types. Moreover, it also helps users to understand the conversion rules, syntax, arguments, usage, and example queries of the CAST function.

What is Redshift?

Amazon web service (AWS) has provided a broad range of products and services that extends solutions from storing enormous data to building enterprise-level applications. Amazon Redshift is one such product primarily released in 2012 to provide Cloud-based, petabyte-scaled Big Data warehousing solutions. Compared to traditional Data Warehouses, Redshift offers cost-effective and lightning-fast performance that enables businesses to deliver productive results. Besides, Redshift uses standard SQL programming at the backend. As a result, it interacts seamlessly with business intelligence tools to generate insights.

Understanding SQL Commands

As a simple text file or CSV format cannot process Big Data in a short duration, organizations store data in a database. A database collects data systematic way that can be used to store and modify information regularly. Despite, availability of several languages, SQL is one of the most widely used programming languages to interact with databases, making it the language of the database. SQL facilitates retrieving information through a combination of English words called queries. Based on the type of information to be fetched, SQL queries are classified into five parts:

1) DCL

Data Control Language (DCL) deals with the authorization of data to a user in a database. DCL command consists of — ‘GRANT’ and ‘REVOKE’ that gives database administrators authority to provide various permissions by limiting the access to other users.

2) DDL

Data Definition Language (DDL) commands deal with the structure of tables residing in a database. DDL commands include — CREATE, ALTER, DROP, and TRUNCATE. All DDL commands are auto-committed, which means they permanently save all changes in working databases.

3) DML

Data Manipulation Language (DML) commands assist you in the modification of data in databases. DML commands include — INSERT, UPDATE, and DELETE. DML commands are not auto-committed, and hence they can be rolled back.

4) DQL

Data Query Language (DQL) is used to retrieve data from a database. It consists of a ‘SELECT’ command to choose desired attributes. SQL clauses are often used with DQL to return specific results from the entire data.

5) TCL

The Transaction Control Logic (TCL) deals with a set of tasks arranged as a single execution unit. The TCL command consists of — ‘COMMIT,’ ‘SAVEPOINT,’ ‘ROLLBACK,’ and ‘SET TRANSACTION.’ While executing a TCL command, each transaction begins with specific tasks and ends when all the relevant tasks are logically evaluated. If any task fails to process, the entire transaction is revived to its previous state.

SQL Data Types and Cast Function

While creating a table, it is the sole responsibility of database developers to decide the name and type of data for each column to be stored. Data type guides SQL to accept only data that meet predefined data types. Below are a few SQL data types:

1) Strings

A string in SQL expects a character of a fixed (CHAR) and varying (VARCHAR) length that allows inserting letters, numbers, and special characters. Depending on the choice of data type, a string stores characters ranging from 0 to 2³²-1 (LONGTEXT).

2) Numerical

Numerical data in SQL deals with numbers that support integers, decimals, boolean expressions, and floating-point numbers. Based on the specified size and type of number, numerical data stores values ranging from 0 to 2⁶⁴-1 (BIGINT).

3) Date and Time

When inserting records, organizations keep track using date and time data type in SQL. Based on the type of format businesses consider, SQL provides — DATE, DATETIME, TIMESTAMP, TIME, and YEAR data type.

4) Binary Large Objects

Sometimes data is not limited to numbers and strings. In such cases, you would need a binary large object (BLOB) data type. BLOB is a collection of binary data stored in a single entity. They usually include — raw files, images, audio, and multimedia objects.

5) CAST Function

The CAST function converts a value from an existing data type into the specified datatype. It can be applied to characters, numbers, data, and time data types. One should note that if you cast a ‘NULL’ to any data type, it would return ‘NULL.’

Redshift Data Type Formatting Functions

Each value that is stored or retrieved in Redshift has a data type with a fixed associated property. As data types are declared when a table is created, each column is constrained. Below is the list of formats supported by Redshift:

• Multibyte Character: is a product of a number of characters and the number of bytes per character. For instance, if a string has four Chinese characters, each character is three bytes and would require VARCHAR(12) to store them in a string.
• Numeric Type: It includes integers, float, and decimal numbers. Integers consist of — smallint, int, and bigint data types to store whole numbers of different ranges. With decimal data type, Redshift provides user-defined precisions up to 38 digits. And while storing variable precision, the float data type can be used. 
• Character Type: It includes CHAR (character) and VARCHAR (character varying); it is defined in terms of bytes, not characters.
• Datetime: It includes — DATE, TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ to store calendar dates and time up to a precision of a fraction of seconds.
• Boolean: is used to store true and false values in a single-byte column.
• HLLSKETCH Type: Amazon Redshift supports HLLSKETCH (HyperLogLog) sketch representations that are either sparse or dense.
• SUPER Type: It helps users to store semistructured data or documents as values in the schemaless form and supports up to 1MB of data for individual SUPER fields or objects.

Type Conversion Rules for Redshift CAST Function

Below are a few conversion rules for the Redshift CAST function:

1. If the data type conversion falls in the same category (such as different numeric data types), they can be implicitly converted. For instance, you can insert a decimal value into a column having an integer data type. Implicit Conversion will round the decimals to produce a whole number at the backend.
2. When incompatible data parses through a Redshift CAST function, it will return an overflow condition as out-of-range-the-range-values are attempted.
3. If the string is an appropriate literal value, you can convert a character string to date, time, timestamp, or numeric value.
4. While converting 64-bit decimal or numeric values to higher precision, an explicit function such as Redshift CAST or CONVERT is used.

Redshift CAST Function

Like other databases, the Redshift CAST function allows run-time conversion between compatible data types using queries. It is mostly used with ‘WHERE,’ ‘HAVING,’ and ‘JOIN’ clauses. 

1) Syntax

The Redshift CAST function consists of two arguments, where the former (expression) consists of the value to be formatted, and the latter (type) defines the output format.

CAST ( expression AS type )
expression :: type

2) Arguments

• Expression: contains column name or literal having one or more values. It cannot contain blank or empty strings.
• Type: consists of one of the supported data types.

3) Limitations

Redshift CAST function returns data type as specified in ‘type’ argument, but may return error or cause overflow during conversion as shown below:

select 123.456::decimal(2,1);

select 12345678::smallint;

It should be noted that the Redshift CAST function cannot be performed on GEOMETRY data type to convert into the desired data type.

Example Queries

Case I: Consider a database having a sales table, and the task is to convert the ‘price paid column from decimal to integer. We use the Redshift CAST function in the following way:

select cast(pricepaid as integer)
from sales where salesid=100;

The above results can also be obtained using an alternative syntax as shown below:

select cast(162.00 as integer) as pricepaid;

Case II: For the above case, ‘saletime’ column consists of a timestamp data type. To cast ‘saletime’ column as date, which is sorted by sales in ascending order, you can use the below query:

select cast(saletime as date), salesid
from sales order by salesid limit 10;

Similarly, there is a ‘caldate’ column in the sales table that consists of date format. In this case, we can cast ‘caldate’ column as timestamps while dateid is sorted in ascending order using the below query:

select cast(caldate as timestamp), dateid
from date order by dateid limit 10;

Case III: In some cases, we cast an integer into a character string. Redshift evaluates such output according to the size of the output string. Below is an example query:

select cast(2008 as char(4));

If you want to cast a number from three decimal places to a single decimal, use the below query:

select cast(109.652 as decimal(4,1));

If there arises a need of increasing the integer part in ‘pricepaid’ column from decimal(8,2) into decimal(38,2), you can use the below query:

select salesid, pricepaid::decimal(38,2)*100000000000000000000
as value from sales where salesid<10 order by salesid;

Conclusion

During traditional data warehouses’ battle in querying large datasets, Amazon Redshift clocks one of the fastest data queries with its massively parallel processing capabilities. Moreover, the Redshift CAST function helps analysts handle various features to simplify working with data to display key insights using BI tools. If you want to export data from various sources into your desired database/destination, like Redshift, then Hevo Data is the right choice for you.
Hevo provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have analysis-ready data at your desired destination. Connect with us today to improve your data management experience and achieve more with your data.

FAQs