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:
1 2 3 | {"a", "b", "c"}, {"1", "2", "3"}, {"apple", "banana", "lemon"} |
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:
1 | B'1010', B'1010101', B'100101' |
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:
1 2 | b'\121\041\126\062' b'\324\643\142\a' |
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:
1 | 'abcd' COLLATE en |
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:
1 2 | DATE '1999-07-24', '2010-03-26' |
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:
1 2 3 | '1.852036907410', '1.012345654321', '1.024681097531', |
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:
1 2 3 | '8.53557292101', '2.80852184', '3.643716849532' |
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:
1 2 | '192.168.1.0', '192.0.0.0/24' |
Here are some examples of IPv6 values:
1 2 | '2001:db8:3333:4444:5555:6666:7777:8888', '2607:f0d0:1002:51::4' |
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:
1 | '32','100','8' |
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:
1 2 | INTERVAL '1 year 2 months 5 hours 30 minutes', INTERVAL '1-6 3 10:30:20' |
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:
1 2 | '{"name": "Brian Green", "gender": "male", "age": "42"}', '{"fruit": "apple", "color": "red", "quantity": "10"}' |
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:
1 | 'abcdefg', 'a1b2c3' |
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:
1 | TIME '05:54:00', TIME '07:40:00' |
TIMESTAMP This data type stores date and time, with a size up to 12 bytes.
Here are some examples of TIMESTAMP
values:
1 2 | TIMESTAMP '2019-10-11', TIMESTAMP '2019-10-11 07:30:20.555555' |
UUID
This data type stores 128-bit hexadecimal UUID values.
Here are some examples of UUID
values:
1 2 3 | '49277d2b494f43737e23453b79', '543a4a6d38332b747e634f5952', '43415130554a4c4b7e55507b55' |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started