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:

stmt_list

no references


stmt_list:

stmt ;

referenced by:


stmt:

alter_table_stmt copy_from_stmt create_stmt delete_stmt drop_stmt explain_stmt help_stmt prepare_stmt execute_stmt deallocate_stmt grant_stmt insert_stmt rename_stmt revoke_stmt savepoint_stmt select_stmt set_stmt show_stmt split_stmt transaction_stmt release_stmt reset_stmt truncate_stmt update_stmt

referenced by:


alter_table_stmt:

ALTER TABLE IF EXISTS relation_expr alter_table_cmds

referenced by:


copy_from_stmt:

COPY qualified_name ( qualified_name_list ) FROM STDIN

referenced by:


create_stmt:

create_database_stmt create_index_stmt create_table_stmt create_table_as_stmt create_user_stmt create_view_stmt

referenced by:


delete_stmt:

DELETE FROM relation_expr_opt_alias where_clause returning_clause

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:

EXPLAIN ( explain_option_list ) explainable_stmt

referenced by:


help_stmt:

HELP unrestricted_name

referenced by:


prepare_stmt:

PREPARE name prep_type_clause AS preparable_stmt

referenced by:


execute_stmt:

EXECUTE name execute_param_clause

referenced by:


deallocate_stmt:

DEALLOCATE PREPARE name ALL

referenced by:


grant_stmt:

GRANT privileges ON targets TO grantee_list

referenced by:


insert_stmt:

INSERT INTO insert_target insert_rest on_conflict UPSERT INTO insert_target insert_rest returning_clause

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:

REVOKE privileges ON targets FROM grantee_list

referenced by:


savepoint_stmt:

SAVEPOINT savepoint_name

referenced by:


select_stmt:

select_no_parens select_with_parens

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 DATABASE COLUMNS INDEX INDEXES CONSTRAINT CONSTRAINTS KEYS FROM CREATE TABLE VIEW var_name DATABASES GRANTS on_privilege_target_clause for_grantee_clause TABLES FROM name TIME ZONE TRANSACTION ISOLATION LEVEL PRIORITY STATUS USERS

referenced by:


split_stmt:

ALTER TABLE qualified_name INDEX table_name_with_index SPLIT AT select_stmt

referenced by:


transaction_stmt:

BEGIN opt_transaction START TRANSACTION opt_transaction_mode_list COMMIT END opt_transaction ROLLBACK opt_to_savepoint

referenced by:


release_stmt:

RELEASE savepoint_name

referenced by:


reset_stmt:

RESET var_name

referenced by:


truncate_stmt:

TRUNCATE opt_table relation_expr_list opt_drop_behavior

referenced by:


update_stmt:

UPDATE relation_expr_opt_alias SET set_clause_list where_clause returning_clause

referenced by:


relation_expr:

qualified_name * ONLY qualified_name ( qualified_name )

referenced by:


alter_table_cmds:

alter_table_cmd ,

referenced by:


qualified_name:

name qname_indirection

referenced by:


qualified_name_list:

qualified_name ,

referenced by:


create_database_stmt:

CREATE DATABASE IF NOT EXISTS name opt_with opt_template_clause opt_encoding_clause opt_lc_collate_clause opt_lc_ctype_clause

referenced by:


create_index_stmt:

CREATE opt_unique INDEX opt_name IF NOT EXISTS name ON qualified_name ( index_params ) opt_storing opt_interleave

referenced by:


create_table_stmt:

CREATE TABLE IF NOT EXISTS any_name ( opt_table_elem_list ) opt_interleave

referenced by:


create_table_as_stmt:

CREATE TABLE IF NOT EXISTS any_name opt_column_list AS select_stmt

referenced by:


create_user_stmt:

CREATE USER name opt_with opt_password

referenced by:


create_view_stmt:

CREATE VIEW any_name opt_column_list AS select_stmt

referenced by:


relation_expr_opt_alias:

relation_expr AS name

referenced by:


where_clause:

WHERE a_expr

referenced by:


returning_clause:

RETURNING target_list NOTHING

referenced by:


name:

identifier unreserved_keyword col_name_keyword

referenced by:


table_name_with_index_list:

table_name_with_index ,

referenced by:


opt_drop_behavior:

CASCADE RESTRICT

referenced by:


table_name_list:

any_name ,

referenced by:


explainable_stmt:

select_stmt create_stmt drop_stmt alter_table_stmt insert_stmt update_stmt delete_stmt show_stmt help_stmt split_stmt

referenced by:


explain_option_list:

explain_option_name ,

referenced by:


unrestricted_name:

identifier unreserved_keyword col_name_keyword type_func_name_keyword reserved_keyword

referenced by:


prep_type_clause:

( type_list )

referenced by:


preparable_stmt:

select_stmt insert_stmt update_stmt delete_stmt

referenced by:


execute_param_clause:

( expr_list )

referenced by:


privileges:

ALL privilege_list

referenced by:


targets:

TABLE table_pattern_list DATABASE name_list

referenced by:


grantee_list:

name ,

referenced by:


insert_target:

qualified_name AS name

referenced by:


insert_rest:

( qualified_name_list ) select_stmt DEFAULT VALUES

referenced by:


on_conflict:

ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list where_clause NOTHING

referenced by:


table_name_with_index:

qualified_name @ name

referenced by:


opt_column:

COLUMN

referenced by:


savepoint_name:

SAVEPOINT name

referenced by:


select_no_parens:

simple_select select_clause sort_clause opt_sort_clause select_limit

referenced by:


select_with_parens:

( select_no_parens select_with_parens )

referenced by:


set_rest:

TRANSACTION transaction_mode_list set_rest_more

referenced by:


transaction_iso_level:

ISOLATION LEVEL iso_level

referenced by:


generic_set:

var_name TO = var_list DEFAULT

referenced by:


any_name:

name . unrestricted_name

referenced by:


var_name:

any_name

referenced by:


on_privilege_target_clause:

ON targets

referenced by:


for_grantee_clause:

FOR grantee_list

referenced by:


opt_transaction:

TRANSACTION

referenced by:


opt_transaction_mode_list:

transaction_iso_level , transaction_user_priority transaction_user_priority , transaction_iso_level

referenced by:


opt_to_savepoint:

TRANSACTION TO savepoint_name

referenced by:


opt_table:

TABLE

referenced by:


relation_expr_list:

relation_expr ,

referenced by:


set_clause_list:

set_clause ,

referenced by:


alter_table_cmd:

ADD COLUMN IF NOT EXISTS column_def table_constraint opt_validate_behavior ALTER opt_column name alter_column_default DROP NOT NULL DROP opt_column CONSTRAINT IF EXISTS name opt_drop_behavior VALIDATE CONSTRAINT name

referenced by:


qname_indirection:

name_indirection_elem

referenced by:


opt_with:

WITH

referenced by:


opt_template_clause:

TEMPLATE opt_equal non_reserved_word_or_sconst

referenced by:


opt_encoding_clause:

ENCODING opt_equal non_reserved_word_or_sconst

referenced by:


opt_lc_collate_clause:

LC_COLLATE opt_equal non_reserved_word_or_sconst

referenced by:


opt_lc_ctype_clause:

LC_CTYPE opt_equal non_reserved_word_or_sconst

referenced by:


opt_unique:

UNIQUE

referenced by:


opt_name:

name

referenced by:


index_params:

index_elem ,

referenced by:


opt_storing:

storing ( name_list )

referenced by:


opt_interleave:

INTERLEAVE IN PARENT name ( name_list )

referenced by:


opt_table_elem_list:

table_elem_list

referenced by:


opt_column_list:

( name_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:

target_elem ,

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:

non_reserved_word

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:

typename ,

referenced by:


expr_list:

a_expr ,

referenced by:


privilege_list:

privilege ,

referenced by:


table_pattern_list:

table_pattern ,

referenced by:


name_list:

name ,

referenced by:


opt_conf_expr:

( name_list ) where_clause

referenced by:


simple_select:

SELECT opt_all_clause DISTINCT target_list from_clause where_clause group_clause having_clause window_clause VALUES ctext_row , TABLE relation_expr select_clause UNION INTERSECT EXCEPT all_or_distinct select_clause

referenced by:


select_clause:

simple_select select_with_parens

referenced by:


sort_clause:

ORDER BY sortby_list

referenced by:


opt_sort_clause:

sort_clause

referenced by:


select_limit:

limit_clause offset_clause offset_clause limit_clause

referenced by:


transaction_mode_list:

transaction_iso_level , transaction_user_priority transaction_user_priority , transaction_iso_level

referenced by:


set_rest_more:

generic_set TIME ZONE zone_value

referenced by:


iso_level:

READ UNCOMMITTED COMMITTED SNAPSHOT REPEATABLE READ SERIALIZABLE

referenced by:


var_list:

var_value ,

referenced by:


transaction_user_priority:

PRIORITY user_priority

referenced by:


set_clause:

single_set_clause multiple_set_clause

referenced by:


column_def:

name typename col_qualification

referenced by:


alter_column_default:

SET DEFAULT a_expr DROP DEFAULT

referenced by:


table_constraint:

CONSTRAINT name constraint_elem

referenced by:


opt_validate_behavior:

NOT VALID

referenced by:


name_indirection_elem:

glob_indirection name_indirection

referenced by:


opt_equal:

=

referenced by:


non_reserved_word_or_sconst:

non_reserved_word SCONST

referenced by:


index_elem:

name opt_asc_desc

referenced by:


storing:

COVERING STORING

referenced by:


table_elem_list:

table_elem ,

referenced by:


c_expr:

d_expr array_subscript case_expr EXISTS select_with_parens

referenced by:


cast_target:

typename postgres_oid

referenced by:


typename:

simple_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:

select_with_parens ( expr_list )

referenced by:


subquery_op:

math_op NOT LIKE ILIKE

referenced by:


sub_type:

ANY SOME ALL

referenced by:


d_expr:

qualified_name a_expr_const PLACEHOLDER ( a_expr ) func_expr select_with_parens ARRAY select_with_parens array_expr explicit_row implicit_row

referenced by:


target_elem:

a_expr AS unrestricted_name identifier *

referenced by:


non_reserved_word:

identifier unreserved_keyword col_name_keyword type_func_name_keyword

referenced by:


privilege:

CREATE DROP GRANT SELECT INSERT DELETE UPDATE

referenced by:


table_pattern:

name name_indirection glob_indirection *

referenced by:


opt_all_clause:

ALL

referenced by:


from_clause:

FROM from_list opt_as_of_clause

referenced by:


group_clause:

GROUP BY expr_list

referenced by:


having_clause:

HAVING a_expr

referenced by:


window_clause:

WINDOW window_definition_list

referenced by:


all_or_distinct:

ALL DISTINCT

referenced by:


sortby_list:

sortby ,

referenced by:


limit_clause:

LIMIT select_limit_value

referenced by:


offset_clause:

OFFSET a_expr c_expr row_or_rows

referenced by:


zone_value:

SCONST identifier interval numeric_only DEFAULT LOCAL

referenced by:


var_value:

opt_boolean_or_string numeric_only PLACEHOLDER

referenced by:


user_priority:

LOW NORMAL HIGH

referenced by:


single_set_clause:

qualified_name = ctext_expr

referenced by:


multiple_set_clause:

( qualified_name_list ) = ctext_row select_with_parens

referenced by:


constraint_elem:

CHECK ( a_expr PRIMARY KEY ( index_params ) UNIQUE ( index_params ) opt_storing opt_interleave FOREIGN KEY ( name_list ) REFERENCES qualified_name opt_column_list

referenced by:


glob_indirection:

. *

referenced by:


name_indirection:

. unrestricted_name

referenced by:


opt_asc_desc:

ASC DESC

referenced by:


table_elem:

column_def index_def family_def table_constraint

referenced by:


case_expr:

CASE case_arg when_clause case_default END

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:


a_expr_const:

ICONST FCONST const_typename INTERVAL ( ICONST ) SCONST BCONST interval TRUE FALSE NULL

referenced by:


func_expr:

func_application filter_clause over_clause func_expr_common_subexpr

referenced by:


array_expr:

[ expr_list array_expr_list ]

referenced by:


explicit_row:

ROW ( expr_list )

referenced by:


implicit_row:

( expr_list , a_expr )

referenced by:


from_list:

table_ref ,

referenced by:


opt_as_of_clause:

AS OF SYSTEM TIME a_expr_const

referenced by:


window_definition_list:

window_definition ,

referenced by:


ctext_row:

( ctext_expr_list )

referenced by:


sortby:

a_expr opt_asc_desc

referenced by:


select_limit_value:

a_expr ALL

referenced by:


row_or_rows:

ROW ROWS

referenced by:


interval:

INTERVAL SCONST opt_interval

referenced by:


numeric_only:

- FCONST signed_iconst

referenced by:


opt_boolean_or_string:

TRUE FALSE ON non_reserved_word_or_sconst

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:


index_def:

UNIQUE INDEX opt_name ( index_params ) opt_storing opt_interleave

referenced by:


family_def:

FAMILY opt_name ( name_list )

referenced by:


array_subscript:

[ a_expr opt_slice_bound : opt_slice_bound ]

referenced by:


case_arg:

a_expr

referenced by:


case_default:

ELSE a_expr

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:


bit:

bit_with_length bit_without_length

referenced by:


character:

character_with_length character_without_length

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:


const_typename:

numeric const_bit const_character const_datetime

referenced by:


func_application:

func_name ( ALL DISTINCT expr_list opt_sort_clause * )

referenced by:


filter_clause:

FILTER ( WHERE a_expr )

referenced by:


over_clause:

OVER window_specification name

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:

array_expr ,

referenced by:


table_ref:

relation_expr opt_index_hints qualified_name ( expr_list ) select_with_parens [ EXPLAIN ( explain_option_list ) explainable_stmt ] opt_ordinality opt_alias_clause joined_table ( joined_table ) opt_ordinality alias_clause

referenced by:


window_definition:

name AS window_specification

referenced by:


ctext_expr_list:

ctext_expr ,

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_slice_bound:

a_expr

referenced by:


when_clause:

WHEN a_expr THEN a_expr

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:

BIT opt_varying

referenced by:


character_with_length:

character_base ( ICONST )

referenced by:


character_without_length:

character_base

referenced by:


const_bit:

bit_with_length bit_without_length

referenced by:


const_character:

character_with_length character_without_length

referenced by:


func_name:

type_function_name name qname_indirection

referenced by:


window_specification:

( opt_existing_window_name opt_partition_clause opt_sort_clause )

referenced by:


extract_list:

extract_arg FROM a_expr expr_list

referenced by:


overlay_list:

a_expr overlay_placing substr_from substr_for expr_list

referenced by:


position_list:

b_expr IN b_expr

referenced by:


substr_list:

a_expr substr_from substr_for substr_for substr_from expr_list

referenced by:


trim_list:

a_expr FROM expr_list

referenced by:


opt_index_hints:

@ unrestricted_name [ ICONST ] { index_hints_param_list }

referenced by:


opt_ordinality:

WITH ORDINALITY

referenced by:


opt_alias_clause:

alias_clause

referenced by:


joined_table:

( joined_table ) table_ref CROSS NATURAL join_type JOIN table_ref join_type JOIN table_ref join_qual

referenced by:


alias_clause:

AS name ( name_list )

referenced by:


opt_name_parens:

( name )

referenced by:


opt_varying:

VARYING

referenced by:


character_base:

CHARACTER CHAR opt_varying VARCHAR STRING

referenced by:


type_function_name:

identifier unreserved_keyword type_func_name_keyword

referenced by:


opt_existing_window_name:

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:

FROM a_expr

referenced by:


substr_for:

FOR a_expr

referenced by:


index_hints_param_list:

index_hints_param ,

referenced by:


join_type:

FULL LEFT RIGHT join_outer INNER

referenced by:


join_qual:

USING ( name_list ) ON a_expr

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