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 theUSE
command to connect to a database:
1 | USE test_db; |
CockroachDB CAST Function
Let’s begin by examining the syntax for the CAST
function:
1 | SELECT CAST ( expression AS TYPE ) |
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:
1 2 | SELECT '100'::INTEGER; SELECT '2.471'::FLOAT; |
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:
1 | SELECT CAST ('1234' AS INTEGER); |
This statement should return results indicating that the string was converted to an INT8
value:
1 2 3 4 | int8 -------- 1234 (1 row) |
CAST Integer to String in SQL
Now, let’s cast an integer as a string. The following statement will perform this conversion:
1 2 3 4 5 | SELECT CAST (1234 AS STRING); text -------- 1234 (1 ROW) |
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:
1 2 3 4 5 | SELECT CAST ('November 15, 2008' AS DATE); DATE ----------------------------- 2008-11-15 00:00:00+00:00 (1 ROW) |
Here’s another example of converting a string value to a date value. This time, the string value has a slightly different format:
1 2 3 4 5 | SELECT CAST ('2008-11-15' AS DATE); DATE ----------------------------- 2008-11-15 00:00:00+00:00 (1 ROW) |
CAST to INTERVAL in SQL
We can also convert a string value to an INTERVAL
data type:
1 2 3 4 5 | SELECT CAST ('1 year 2 months 7 days' AS INTERVAL); INTERVAL ------------------------ 1 YEAR 2 mons 7 days (1 ROW) |
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:
1 2 3 | CREATE TABLE numbers ( num STRING ); |
Once our table is created, we can insert records into the table:
1 2 | INSERT INTO numbers VALUES ('1'), ('10.1'), ('2.182'); |
Now, let’s cast the values from the table as integers:
1 | SELECT CAST(num AS INTEGER) FROM numbers; |
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:
1 2 | ERROR: could NOT parse "10.1" AS TYPE INT: strconv.ParseInt: parsing "10.1": invalid syntax SQLSTATE: 22P02 |
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:
1 2 3 4 5 6 7 | SELECT CAST(num AS DECIMAL) FROM numbers; num --------- 1 10.1 2.182 (3 ROWS) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started