SQL Grammar

Warning:
As of November 10, 2018, CockroachDB v1.0 is no longer supported. For more details, refer to the Release Support Policy.
Tip:

This page describes the full CockroachDB SQL grammar. However, as a starting point, it's best to reference our SQL statements pages first, which provide detailed explanations and examples.

stmt_block:

no references


stmt_list:

referenced by:


stmt:

referenced by:


alter_table_stmt:

referenced by:


backup_stmt:

referenced by:


copy_from_stmt:

referenced by:


create_stmt:

referenced by:


delete_stmt:

referenced by:


drop_stmt:

DROP DATABASE IF EXISTS name INDEX IF EXISTS table_name_with_index_list TABLE VIEW IF EXISTS table_name_list opt_drop_behavior

referenced by:


explain_stmt:

referenced by:


help_stmt:

referenced by:


prepare_stmt:

referenced by:


execute_stmt:

referenced by:


deallocate_stmt:

DEALLOCATE PREPARE name ALL

referenced by:


grant_stmt:

referenced by:


insert_stmt:

referenced by:


rename_stmt:

ALTER DATABASE name INDEX IF EXISTS table_name_with_index RENAME TO name TABLE IF EXISTS relation_expr RENAME TO qualified_name opt_column name TO name VIEW IF EXISTS relation_expr RENAME TO qualified_name

referenced by:


revoke_stmt:

referenced by:


savepoint_stmt:

referenced by:


select_stmt:

referenced by:


set_stmt:

SET set_rest SESSION CHARACTERISTICS AS TRANSACTION transaction_iso_level set_rest CLUSTER SETTING generic_set

referenced by:


show_stmt:

SHOW identifier ALL CLUSTER SETTINGS CLUSTER SETTING any_name ALL COLUMNS INDEX INDEXES CONSTRAINT CONSTRAINTS KEYS FROM CREATE TABLE VIEW var_name DATABASES GRANTS on_privilege_target_clause for_grantee_clause TABLES FROM name USERS

referenced by:


split_stmt:

referenced by:


transaction_stmt:

referenced by:


release_stmt:

referenced by:


reset_stmt:

referenced by:


truncate_stmt:

referenced by:


update_stmt:

referenced by:


relation_expr:

referenced by:


alter_table_cmds:

referenced by:


targets:

referenced by:


string_or_placeholder:

referenced by:


opt_as_of_clause:

AS OF SYSTEM TIME a_expr_const

referenced by:


opt_incremental:

referenced by:


opt_with_options:

WITH OPTIONS ( kv_option_list )

referenced by:


string_or_placeholder_list:

referenced by:


qualified_name:

referenced by:


qualified_name_list:

referenced by:


create_database_stmt:

referenced by:


create_index_stmt:

referenced by:


create_table_stmt:

referenced by:


create_table_as_stmt:

CREATE TABLE IF NOT EXISTS any_name opt_column_list AS select_stmt

referenced by:


create_user_stmt:

referenced by:


create_view_stmt:

referenced by:


relation_expr_opt_alias:

referenced by:


where_clause:

WHERE a_expr

referenced by:


returning_clause:

RETURNING target_list NOTHING

referenced by:


name:

referenced by:


table_name_with_index_list:

referenced by:


opt_drop_behavior:

CASCADE RESTRICT

referenced by:


table_name_list:

referenced by:


explainable_stmt:

referenced by:


explain_option_list:

referenced by:


unrestricted_name:

referenced by:


prep_type_clause:

referenced by:


preparable_stmt:

referenced by:


execute_param_clause:

referenced by:


privileges:

referenced by:


grantee_list:

referenced by:


insert_target:

referenced by:


insert_rest:

referenced by:


on_conflict:

ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list where_clause NOTHING

referenced by:


table_name_with_index:

referenced by:


opt_column:

COLUMN

referenced by:


savepoint_name:

SAVEPOINT name

referenced by:


select_no_parens:

referenced by:


select_with_parens:

referenced by:


set_rest:

referenced by:


transaction_iso_level:

ISOLATION LEVEL iso_level

referenced by:


generic_set:

referenced by:


any_name:

referenced by:


var_name:

referenced by:


on_privilege_target_clause:

referenced by:


for_grantee_clause:

referenced by:


opt_transaction:

TRANSACTION

referenced by:


opt_transaction_mode_list:

referenced by:


opt_to_savepoint:

TRANSACTION TO savepoint_name

referenced by:


opt_table:

TABLE

referenced by:


relation_expr_list:

referenced by:


set_clause_list:

referenced by:


alter_table_cmd:

referenced by:


table_pattern_list:

referenced by:


name_list:

referenced by:


non_reserved_word_or_sconst:

referenced by:


a_expr_const:

ICONST FCONST const_typename SCONST BCONST interval TRUE FALSE NULL

referenced by:


kv_option_list:

referenced by:


qname_indirection:

referenced by:


opt_with:

WITH

referenced by:


opt_template_clause:

referenced by:


opt_encoding_clause:

referenced by:


opt_lc_collate_clause:

referenced by:


opt_lc_ctype_clause:

referenced by:


opt_unique:

UNIQUE

referenced by:


opt_name:

referenced by:


index_params:

referenced by:


opt_storing:

referenced by:


opt_interleave:

INTERLEAVE IN PARENT name ( name_list )

referenced by:


opt_table_elem_list:

referenced by:


opt_column_list:

referenced by:


opt_password:

PASSWORD SCONST

referenced by:


a_expr:

c_expr a_expr TYPECAST cast_target TYPEANNOTATE typename COLLATE any_name + - * / FLOORDIV % ^ # & | < > = CONCAT LSHIFT RSHIFT LESS_EQUALS GREATER_EQUALS NOT_EQUALS BETWEEN opt_asymmetric SYMMETRIC b_expr AND OR LIKE ILIKE SIMILAR TO ~ NOT_REGMATCH REGIMATCH NOT_REGIMATCH a_expr NOT LIKE ILIKE SIMILAR TO BETWEEN opt_asymmetric SYMMETRIC b_expr AND a_expr IN in_expr IS NOT NAN NULL TRUE FALSE UNKNOWN DISTINCT FROM a_expr OF ( type_list ) IN in_expr subquery_op sub_type d_expr + - ~ NOT a_expr

referenced by:


target_list:

referenced by:


unreserved_keyword:

ACTION ADD ALTER AT BACKUP BEGIN BLOB BY CASCADE CLUSTER COLUMNS COMMIT COMMITTED CONFLICT CONSTRAINTS COPY COVERING CUBE CURRENT CYCLE DATA DATABASE DATABASES DAY DEALLOCATE DELETE DOUBLE DROP ENCODING EXECUTE EXPLAIN FILTER FIRST FOLLOWING FORCE_INDEX GRANTS HELP HIGH HOUR INCREMENTAL INDEXES INSERT INT2VECTOR INTERLEAVE ISOLATION KEY KEYS LC_COLLATE LC_CTYPE LEVEL LOCAL LOW MATCH MINUTE MONTH NAMES NAN NEXT NO NORMAL NO_INDEX_JOIN NULLS OF OFF OID OPTIONS ORDINALITY OVER PARENT PARTIAL PARTITION PASSWORD PRECEDING PREPARE PRIORITY RANGE READ RECURSIVE REF REGCLASS REGPROC REGPROCEDURE REGNAMESPACE REGTYPE RELEASE RENAME REPEATABLE RESET RESTORE RESTRICT REVOKE ROLLBACK ROLLUP ROWS SETTING SETTINGS STATUS SAVEPOINT SCATTER SEARCH SECOND SERIALIZABLE SESSION SET SHOW SIMPLE SNAPSHOT SQL START STDIN STORING STRICT SPLIT SYSTEM TABLES TEMPLATE TESTING_RANGES TESTING_RELOCATE TEXT TRANSACTION TRUNCATE TYPE UNBOUNDED UNCOMMITTED UNKNOWN UPDATE UPSERT USERS VALID VALIDATE VALUE VARYING WITHIN WITHOUT YEAR ZONE

referenced by:


col_name_keyword:

ANNOTATE_TYPE BETWEEN BIGINT BIGSERIAL BIT BOOL BOOLEAN BYTEA BYTES CHAR CHARACTER CHARACTERISTICS COALESCE DATE DEC DECIMAL EXISTS EXTRACT EXTRACT_DURATION FLOAT GREATEST GROUPING IF IFNULL INT INT8 INT64 INTEGER INTERVAL LEAST NAME NULLIF NUMERIC OUT OVERLAY POSITION PRECISION REAL ROW SERIAL SMALLINT SMALLSERIAL STRING SUBSTRING TIME TIMESTAMP TIMESTAMPTZ TREAT TRIM VALUES VARCHAR

referenced by:


explain_option_name:

referenced by:


type_func_name_keyword:

COLLATION CROSS FULL INNER ILIKE IS JOIN LEFT LIKE NATURAL OUTER OVERLAPS RIGHT SIMILAR

referenced by:


reserved_keyword:

ALL ANALYSE ANALYZE AND ANY ARRAY AS ASC ASYMMETRIC BOTH CASE CAST CHECK COLLATE COLUMN CONSTRAINT CREATE CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER DEFAULT DEFERRABLE DESC DISTINCT DO ELSE END EXCEPT FALSE FAMILY FETCH FOR FOREIGN FROM GRANT GROUP HAVING IN INDEX INITIALLY INTERSECT INTO LATERAL LEADING LIMIT LOCALTIME LOCALTIMESTAMP NOT NOTHING NULL OFFSET ON ONLY OR ORDER PLACING PRIMARY REFERENCES RETURNING SELECT SESSION_USER SOME SYMMETRIC TABLE THEN TO TRAILING TRUE UNION UNIQUE USER USING VARIADIC VIEW WHEN WHERE WINDOW WITH

referenced by:


type_list:

referenced by:


expr_list:

referenced by:


privilege_list:

referenced by:


opt_conf_expr:

referenced by:


simple_select:

referenced by:


select_clause:

referenced by:


sort_clause:

ORDER BY sortby_list

referenced by:


opt_sort_clause:

referenced by:


select_limit:

referenced by:


transaction_mode_list:

referenced by:


set_rest_more:

referenced by:


iso_level:

READ UNCOMMITTED COMMITTED SNAPSHOT REPEATABLE READ SERIALIZABLE

referenced by:


var_list:

referenced by:


transaction_user_priority:

referenced by:


set_clause:

referenced by:


column_def:

referenced by:


alter_column_default:

SET DEFAULT a_expr DROP DEFAULT

referenced by:


table_constraint:

referenced by:


opt_validate_behavior:

NOT VALID

referenced by:


table_pattern:

referenced by:


non_reserved_word:

referenced by:


const_typename:

referenced by:


interval:

INTERVAL SCONST opt_interval

referenced by:


kv_option:

referenced by:


name_indirection_elem:

referenced by:


opt_equal:

=

referenced by:


index_elem:

referenced by:


storing:

COVERING STORING

referenced by:


table_elem_list:

referenced by:


c_expr:

referenced by:


cast_target:

referenced by:


typename:

referenced by:


opt_asymmetric:

ASYMMETRIC

referenced by:


b_expr:

c_expr b_expr TYPECAST cast_target TYPEANNOTATE typename + - * / FLOORDIV % ^ # & | < > = CONCAT LSHIFT RSHIFT LESS_EQUALS GREATER_EQUALS NOT_EQUALS b_expr IS NOT DISTINCT FROM b_expr OF ( type_list ) + - ~ b_expr

referenced by:


in_expr:

referenced by:


subquery_op:

math_op NOT LIKE ILIKE

referenced by:


sub_type:

ANY SOME ALL

referenced by:


d_expr:

referenced by:


target_elem:

referenced by:


privilege:

CREATE DROP GRANT SELECT INSERT DELETE UPDATE

referenced by:


opt_all_clause:

ALL

referenced by:


from_clause:

referenced by:


group_clause:

GROUP BY expr_list

referenced by:


having_clause:

HAVING a_expr

referenced by:


window_clause:

referenced by:


all_or_distinct:

ALL DISTINCT

referenced by:


sortby_list:

referenced by:


limit_clause:

referenced by:


offset_clause:

referenced by:


var_value:

referenced by:


user_priority:

LOW NORMAL HIGH

referenced by:


single_set_clause:

referenced by:


multiple_set_clause:

referenced by:


constraint_elem:

referenced by:


name_indirection:

referenced by:


glob_indirection:

. *

referenced by:


numeric:

INT INT8 INT64 INTEGER SMALLINT BIGINT REAL FLOAT opt_float DOUBLE PRECISION DECIMAL DEC NUMERIC opt_numeric_modifiers BOOLEAN BOOL

referenced by:


const_bit:

referenced by:


const_character:

referenced by:


const_datetime:

DATE TIMESTAMP WITHOUT WITH TIME ZONE TIMESTAMPTZ

referenced by:


opt_interval:

YEAR TO MONTH MONTH DAY TO HOUR MINUTE SECOND HOUR TO MINUTE SECOND MINUTE TO SECOND SECOND

referenced by:


opt_equal_value:

= SCONST

referenced by:


opt_asc_desc:

ASC DESC

referenced by:


table_elem:

referenced by:


case_expr:

referenced by:


postgres_oid:

REGPROC REGPROCEDURE REGCLASS REGTYPE REGNAMESPACE

referenced by:


simple_typename:

numeric bit character const_datetime INTERVAL opt_interval BLOB BYTES BYTEA TEXT NAME SERIAL SMALLSERIAL BIGSERIAL OID INT2VECTOR

referenced by:


math_op:

+ - * / FLOORDIV % & | ^ # < > = LESS_EQUALS GREATER_EQUALS NOT_EQUALS

referenced by:


func_expr:

referenced by:


array_expr:

referenced by:


explicit_row:

ROW ( expr_list )

referenced by:


implicit_row:

referenced by:


from_list:

referenced by:


window_definition_list:

referenced by:


ctext_row:

referenced by:


sortby:

referenced by:


select_limit_value:

referenced by:


row_or_rows:

ROW ROWS

referenced by:


opt_boolean_or_string:

referenced by:


numeric_only:

referenced by:


ctext_expr:

a_expr DEFAULT

referenced by:


col_qualification:

CONSTRAINT name col_qualification_elem COLLATE any_name FAMILY name CREATE FAMILY opt_name IF NOT EXISTS FAMILY name

referenced by:


opt_float:

( ICONST )

referenced by:


opt_numeric_modifiers:

( ICONST , ICONST )

referenced by:


bit_with_length:

BIT opt_varying ( ICONST )

referenced by:


bit_without_length:

referenced by:


character_with_length:

character_base ( ICONST )

referenced by:


character_without_length:

referenced by:


index_def:

referenced by:


family_def:

referenced by:


array_subscript:

referenced by:


case_arg:

referenced by:


case_default:

referenced by:


bit:

referenced by:


character:

referenced by:


func_application:

referenced by:


filter_clause:

FILTER ( WHERE a_expr )

referenced by:


over_clause:

referenced by:


func_expr_common_subexpr:

CURRENT_DATE CURRENT_TIMESTAMP ( ) CAST ( a_expr AS cast_target ANNOTATE_TYPE ( a_expr , typename EXTRACT EXTRACT_DURATION ( extract_list OVERLAY ( overlay_list POSITION ( position_list SUBSTRING ( substr_list IF ( a_expr , NULLIF IFNULL ( a_expr , a_expr COALESCE GREATEST LEAST ( expr_list TRIM ( BOTH LEADING TRAILING trim_list )

referenced by:


array_expr_list:

referenced by:


table_ref:

referenced by:


window_definition:

referenced by:


ctext_expr_list:

referenced by:


signed_iconst:

+ - ICONST

referenced by:


col_qualification_elem:

NOT NULL UNIQUE PRIMARY KEY CHECK ( a_expr ) DEFAULT b_expr REFERENCES qualified_name opt_name_parens

referenced by:


opt_varying:

VARYING

referenced by:


character_base:

CHARACTER CHAR opt_varying VARCHAR STRING

referenced by:


opt_slice_bound:

referenced by:


when_clause:

referenced by:


func_name:

referenced by:


window_specification:

referenced by:


extract_list:

referenced by:


overlay_list:

referenced by:


position_list:

referenced by:


substr_list:

referenced by:


trim_list:

referenced by:


opt_index_hints:

referenced by:


opt_ordinality:

WITH ORDINALITY

referenced by:


opt_alias_clause:

referenced by:


joined_table:

referenced by:


alias_clause:

referenced by:


opt_name_parens:

( name )

referenced by:


type_function_name:

referenced by:


opt_existing_window_name:

referenced by:


opt_partition_clause:

PARTITION BY expr_list

referenced by:


extract_arg:

identifier YEAR MONTH DAY HOUR MINUTE SECOND

referenced by:


overlay_placing:

PLACING a_expr

referenced by:


substr_from:

referenced by:


substr_for:

referenced by:


index_hints_param_list:

referenced by:


join_type:

FULL LEFT RIGHT join_outer INNER

referenced by:


join_qual:

referenced by:


index_hints_param:

FORCE_INDEX = unrestricted_name NO_INDEX_JOIN

referenced by:


join_outer:

OUTER

referenced by:


generated by Railroad Diagram Generator


Yes No

Product

Resources

Learn

Support Channels

Company

Get developer news