Create a Table in CockroachDB

Introduction

CockroachDB has a built-in SQL interface client that uses much of the same syntax and format as many SQL-based relational database systems, including PostgreSQL, MariaDB and MySQL. The CockroachDB interface is designed to execute SQL statements directly from the command line or an interactive shell. This tutorial will provide an overview of the SQL statement CREATE TABLE for a CockroachDB, including how to use the CREATE TABLE SQL statement in the SQL client to create a table in CockroachDB.

Prerequisites

• A CockroachDB cluster must running on at least one server node to create a table in CockroachDB. For local development on macOS X, Cockroach with Homebrew can be installed with the following brew command:

• Linux users will need to install CockroachDB and run the cluster. The binary can be downloaded using cURL and use this link to install CockroachDB from scratch.

Access the SQL client for CockroachDB

SSH access to the server is required before being able to access the SQL client for Cockroach.

Irrelevant of the OS being used to run CockroachDB, the following command should access the SQL client:

Create a table in CockroachDB

Once inside of the SQL client, create a table for the CockroachDB database. If a database has not yet been created, use the CREATE DATABASE command followed by the database name.

Remember, to avoid any errors, all SQL statements must end with a semi-colon (;).

Set the CockroachDB database

The database must be set before a table can be create for it. Use the SQL SET keyword followed by the database = identifier to set the name as follows:

NOTE: Spacing and quotation marks around the database name are optional, however, in this case CockroachDB will raise a "pq" driver error if the database doesn’t exist.

CockroachDB also allows for other SQL statements and commands as aliases for those users more familiar with PostgreSQL or MySQL. For example, the USE statement can be used to set the database similarly as to other relational database management systems, as shown here:

Now a table for the database can be created.

Using the CREATE TABLE SQL statement in CockroachDB

The CREATE TABLE SQL statement can be used to create a table to hold the CockroachDB data. As with other SQL-based languages, the columns for the table are enclosed in parenthesis and are delimited by commas as shown in the following example:

NOTE: Make certain the first word for the column is the column name itself with the column name followed by the data type and any option, such as the 64 byte length limit for the str_col column in the above example.

CockroachDB invalid syntax

An invalid syntax error, as shown below, usually means that the column name, a comma or the data type for the column was omitted:

If this occurs, carefully examine the entire CREATE TABLE statement to confirm the SQL syntax for all of the column names and data types are correct.

Another CREATE TABLE SQL example for CockroachDB

This example will explain how to create a copy of the above table. However, this instance will only grab the id column using the AS SQL clause in conjunction with the following SELECT statement:

The results of the above SQL command should resemble the following:

Show the CockroachDB table columns

Unlike in MySQL, the DESCRIBE statement will not work in CockroachDB. In order to get the columns for the table, use the SHOW COLUMNS statement instead followed by the FROM clause and the table name as shown here:

As the new copy_table only copied the id column from the original table, as shown below, the above SQL statement should only return two rows of column names.

Drop table in CockroachDB

Since a table with just an ID or key column has limited uses, delete (or DROP) the copy_table by executing the following DROP TABLE SQL statement:

The above command should result in a DROP TABLE response.

Conclusion

This tutorial provided an overview of the SQL statement CREATE TABLE for a CockroachDB for the SQL client interface for CockroachDB databases. The article covered how to create a table in CockroachDB, use the CREATE TABLE SQL statement, provided a CREATE TABLE SQL example, explained the CockroachDB invalid syntax error and how to set the CockroachDB database. The tutorial also explained how to access the SQL client for CockroachDB and how to delete a table. Remember to end all SQL statements with a semi-colon in order to avoid errors.