Understanding the CockroachDB Data Types

Introduction

If you want to harness the full power of CockroachDB, you’ll need to understand the various data types that can be used. It’s especially important to have a good understanding of CockroachDB data types when you’re defining the columns in a table. In this article, we’ll discuss the various CockroachDB data types and explain what kind of values each one can accept.

Prerequisites

Before you proceed with this article, two key prerequisites must be in place:

• CockroachDB must be installed and configured on your machine.
• You should have some basic experience using CockroachDB.

CockroachDB Data Types

In CockroachDB, data types are used to define the kind of data or values that a column can accept.

Let’s take a look at the supported data types in CockroachDB:

ARRAY This data type stores one-indexed, one-dimensional homogeneous arrays of any non-array data type. The size is variable, but values should be under 1 MB.

Here are some examples of ARRAY values:

BIT This data type stores a string of binary digits where the length of the bits is fixed. For the VARBIT type, which has an alias of BIT VARYING, the length may be variable.

Here are some examples of BIT values:

BOOL This data type stores Boolean values of TRUE and FALSE. The size of these values is one byte in width.

BYTES This data type stores a string of binary characters. While the size is variable, values should be under 1 MB. Two aliases for BYTES include BYTEA and BLOB.

Here are some examples of BYTES values:

COLLATE This data type sorts STRING values according to language- and country-specific rules known as collations.

Here’s an example of the COLLATE data type:

DATE The DATE data type stores date values that include day, month and year. The format for DATE values is YYYY-MM-DD. The size of the values can be up to 16 bytes.

Here are some examples of DATE values:

DECIMAL This data type stores exact and fixed-point numeric values. Two aliases for DECIMAL include DEC and NUMERIC. The size is variable starting with 9 bytes; however, values should be less than 64 kilobytes.

Here are some examples of DECIMAL values:

FLOAT This data type stores inexact, floating-point numeric values. The data type name FLOAT4 is an alias for REAL, and the name FLOAT8 is an alias for DOUBLE PRECISION. The size of this data type’s values can be up to 8 bytes.

Here are some examples of FLOAT values:

INET The INET data type stores an IPv4 address with a size of 32 bits; it can also store an IPv6 address with a size of 128 bits.

Here are some examples of IPv4 values for the INET data type:

Here are some examples of IPv6 values:

INT

This data type stores integer values with a size up to 64 bits. The INT data type has several aliases, including INTEGER, INT8, INT64 and BIGINT. The BIGINT type has a size of 64 bits, and values can range from -9223372036854775807 to +9223372036854775807. The INT2 type has an alias of SMALLINT, with a size of 16 bits and values that range from -32768 to +32767. The INT4 type has a size of 32 bits, with values that range from -2147483648 to +2147483647.

Here are some examples of INT values:

INTERVAL The INTERVAL data type stores values that signify some span of time. The size can be up to 24 bits.

Here are some examples of INTERVAL values:

JSON/JSONB This data type stores JSON (JavaScript Object Notation) values. The size of this type’s values is variable but should remain under 1 MB.

Here are some examples of JSON values:

SERIAL This data type stores automatically-generated integer values with a default expression. The size of this type’s values is variable: The INT2 type has serial variants of SMALL SERIAL and SERIAL2, the INT4 type has a serial variant of SERIAL4, the INT type has a serial variant of SERIAL, and the INT8 data type has serial variants of SERIAL8 and BIGSERIAL.

STRING This data type stores a string of Unicode characters, where the size is variable but must remain under 64 kilobytes. The aliases for the STRING type are CHARACTER, CHAR, VARIABLE and TEXT.

Here are some examples of STRING values:

TIME The TIME data type stores values that indicate time of day without timezone. The size can be up to 8 bytes.

Here are some examples of TIME values:

TIMESTAMP This data type stores date and time, with a size up to 12 bytes.

Here are some examples of TIMESTAMP values:

UUID

This data type stores 128-bit hexadecimal UUID values.

Here are some examples of UUID values:

Conclusion

When you’re getting started with CockroachDB, it’s important to take a moment and get acquainted with the various data types supported by the system. Being familiar with these data types allows you to get the most out of CockroachDB and structure your tables correctly. In this article, we provided an overview of all the CockroachDB data types and presented examples of each type. With this tutorial to serve as a reference, you’ll be able to use the CockroachDB data types effectively in your own database tables.