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:
1 | brew install cockroach |
- 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:
1 | cockroach sql --insecure |
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:
1 | SET DATABASE = db_name; |
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:
1 | USE db_name; |
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:
1 2 3 4 5 6 | CREATE TABLE some_table ( id INTEGER PRIMARY KEY, str_col VARCHAR(64), int_col INTEGER, bool_col BOOLEAN ); |
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:
1 | invalid syntax: statement ignored: at or near... |
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:
1 2 | CREATE TABLE copy_table AS (SELECT id FROM some_table); |
The results of the above SQL command should resemble the following:
1 2 3 | CREATE TABLE AS TIME: 28.514162ms |
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:
1 | SHOW COLUMNS FROM copy_table; |
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:
1 | DROP TABLE copy_table; |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started