Data Types in CockroachDB

In this chapter, we will delve into the diverse range of data types available within CockroachDB. Understanding and using these appropriately plays a pivotal role in how efficiently our database operates and allows us to ensure that each piece of data is stored in its optimal manner. Utilizing the correct data types also helps with data validation, ensuring that erroneous or meaningless data do not get stored inadvertently. We'll use movie-themed examples to illustrate the data types and usage better.

Integer Data Type

The integer data type in CockroachDB is used to store whole numbers. We have INT (or INTEGER), SMALLINT,BIGINT, and SERIAL for auto-incrementing integers.

In the following example, we're creating a table called movies where movie_id is an auto-incrementing integer and release_year is also an integer.

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    release_year INT
);

Decimal and Float

The decimal and float data types in CockroachDB are used to store fractional numbers. DECIMAL or NUMERIC can store exact fractional values while FLOAT stores approximate fractional values.

The imdb_score field could be a decimal, as IMDB scores often involve decimal points:

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    imdb_score DECIMAL(3,2)
);

String Data Types

The string data types in CockroachDB are used to store textual data. We have STRING and VARCHAR for variable length strings, and CHAR for fixed length strings.

For instance, we can store the name of the director of a movie in a VARCHAR field:

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    director VARCHAR(100)
);

Boolean Data Type

Boolean data types in CockroachDB are used to store true or false values. We use BOOL or BOOLEAN.

We could use this to add a field to our movies table called is_color:

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    is_color BOOLEAN
);

Date and Time Data Types

CockroachDB supports various date and time data types including DATE, TIME, TIMESTAMP, and INTERVAL.

Let's add a release_date to our movies table using the DATE data type:

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    release_date DATE
);

Enum Data Type

Enum data type in CockroachDB can contain a static, ordered set of values. We use ENUM.

We can use an ENUM to create a new data type for movie genres:

CREATE TYPE genre AS ENUM (
  'Action', 'Adventure', 'Animation', 'Biography', 'Comedy', 'Crime', 'Documentary', 'Drama', 'Family', 'Fantasy', 'Horror', 'Musical', 'Mystery', 'Romance', 'Sci-Fi', 'Thriller', 'War', 'Western'
);

We can then use this type in our movies table:

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    genre genre
);

UUID Data Type

UUID is used to store Universally Unique Identifiers (UUID). CockroachDB also allows the generation of a UUID when inserting data, using the uuid_v4() function.

CREATE TABLE movies (
    movie_id UUID PRIMARY KEY DEFAULT uuid_v4()::UUID,
    title STRING
);

ARRAY Data Type

The ARRAY data type is used to store an array of a specific type. This could be useful for storing multiple related values in a single column.

We might want to store the names of all the main characters in a movie, and we can do this using an array:

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    main_characters ARRAY(STRING)
);

JSON Data Types

CockroachDB offers two types for storing JSON data, JSON and JSONB. JSONB stores JSON data in a binary format for quicker querying, while JSON stores the data as plain text.

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title STRING,
    movie_json JSONB
);

In conclusion, CockroachDB provides a wide variety of data types that cater to almost any type of data that you will need to store. This flexibility, along with the ability to use arrays, and JSON objects, makes CockroachDB well suited to handle complex, modern data structures. Understanding and leveraging the right data types can lead to more efficient storage, faster querying, and more potent data validation.