Create a Table in CockroachDB
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.
- 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 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:
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:
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:
Now a table for the database can be created.
Using the CREATE TABLE SQL statement in CockroachDB
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:
CREATE TABLE some_table (
id INTEGER PRIMARY KEY,
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:
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
CREATE TABLE copy_table AS
(SELECT id FROM some_table);
The results of the above SQL command should resemble the following:
CREATE TABLE AS
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:
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
copy_table by executing the following
DROP TABLE SQL statement:
DROP TABLE copy_table;
The above command should result in a
DROP TABLE response.
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