Understanding the CockroachDB CAST Function

Introduction

When you construct queries in CockroachDB, there may be times when it makes sense to convert a certain value to a different data type. For example, you may want to convert a numeric value stored as a string to an integer data type– doing this would allow you to perform calculations on the value. The CockroachDB CAST function can be used to convert the data type of a value to another type, as long as the value is compatible with the targeted type. If the CAST function is unable to convert a value, it will return an error. In this article, we’ll explain how to use the CockroachDB CAST function and review examples of different data type conversions.

Prerequisites

Before moving forward with this tutorial, make sure that the following prerequisites are in place:

• CockroachDB must be installed and configured on your machine.

• You’ll need to have some basic knowledge of CockroachDB in order to follow along with the examples in this article. You can connect to your CockroachDB database using the cockroach sql command-line interface.

• The \c command that’s used in psql can’t be used in the CockroachDB SQL interface. Instead, you can execute the USE command to connect to a database:

CockroachDB CAST Function

Let’s begin by examining the syntax for the CAST function:

In this syntax, the CAST function is specified after the SELECT keyword. The expression comes next inside the parentheses, followed by the targeted data type. The value you want to cast must be compatible with the targeted data type, and the targeted data type must be supported by CockroachDB. If the value is incompatible or the type is not valid, the CAST function will return an error.

Another way to CAST values involves using :: notation between the value and the specified data type. The following examples show this alternative way to CAST values:

SQL CAST Function Examples

Next, let’s look at some examples that use the CAST function to convert the data type of one value to another type.

CAST String to Integer in SQL

In our first example, we’ll convert the data type of a string value to an integer value. The following example demonstrates how it’s done:

This statement should return results indicating that the string was converted to an INT8 value:

CAST Integer to String in SQL

Now, let’s cast an integer as a string. The following statement will perform this conversion:

The integer value is now cast as a string value.

CAST to DATE in SQL

By using the CAST function, you can convert a value to a DATE data type.

The following statement will convert a string to a date:

Here’s another example of converting a string value to a date value. This time, the string value has a slightly different format:

CAST to INTERVAL in SQL

We can also convert a string value to an INTERVAL data type:

We can see that the data type is now changed to INTERVAL.

CockroachDB CAST Values From a Table

Next, let’s look at an example that uses the CAST function with a table. We’ll create a table and name it numbers. It will contain a column with a string data type:

Once our table is created, we can insert records into the table:

Now, let’s cast the values from the table as integers:

In this case, an error would be returned. This is because there is a decimal among the string values which cannot be converted to an INTEGER value:

Let’ s try to cast it again, but this time we’ll choose the DECIMAL type to match the values of the records inserted into the table:

Conclusion

Being able to convert CockroachDB column values from one data type to another can provide valuable flexibility, allowing you to perform various calculations and processing operations on the data that wouldn’t be possible otherwise. In this article, we learned how to perform these data type conversions using the CockroachDB CAST function. With our examples to get you started, you’ll be able to cast data types in your own CockroachDB tables.