SQL Standard Compliance

This section provides a list of features that CrateDB supports and to what extent it conforms to the current SQL standard ISO/IEC 9075 “Database Language SQL”.

This list is approximate and features that are listed as supported might be nonconforming in their implementation. However, the main reference documentation of CrateDB always contains the most accurate information about what CrateDB supports, what they are and how to use them.

IDPackage#DescriptionComments
E011Numeric data types4Arithmetic operators 
E011Numeric data types5Numeric comparison 
E011Numeric data types6Implicit casting among the numeric data types 
E021Character string types3Character literalsOnly simple ‘ quoting
E021Character string types4CHARACTER_LENGTH functionchar_length only
E021Character string types5OCTET_LENGTH function 
E021Character string types7Character concatenation 
E021Character string types8UPPER and LOWER functions 
E021Character string types10Implicit casting among the character string typesjust one type
E021Character string types12Character comparison 
E031Identifiers   
E031Identifiers1Delimited identifiers 
E031Identifiers2Lower case identifiers 
E031Identifiers3Trailing underscore 
E051Basic query specification1SELECT DISTINCT 
E051Basic query specification2GROUP BY clause 
E051Basic query specification4GROUP BY can contain columns not in <select list> 
E051Basic query specification5Select list items can be renamed 
E051Basic query specification6HAVING clause 
E051Basic query specification7Qualified * in select list 
E051Basic query specification8Correlation names in the FROM clause 
E061Basic predicates and search conditions1Comparison predicate 
E061Basic predicates and search conditions2BETWEEN predicate 
E061Basic predicates and search conditions3IN predicate with list of values 
E061Basic predicates and search conditions4LIKE predicate 
E061Basic predicates and search conditions6NULL predicate 
E061Basic predicates and search conditions14Search condition 
E071Basic query expressions2UNION ALL table operator 
E081Basic Privileges   
E081Basic Privileges1SELECT privilege 
E081Basic Privileges2DELETE privilege 
E091Set functions1AVG 
E091Set functions2COUNT 
E091Set functions3MAX 
E091Set functions4MIN 
E091Set functions5SUM 
E091Set functions7DISTINCT quantifier 
E101Basic data manipulation   
E101Basic data manipulation1INSERT statement 
E101Basic data manipulation3Searched UPDATE statement 
E101Basic data manipulation4Searched DELETE statement 
E131Null value support (nulls in lieu of values)   
E141Basic integrity constraints1NOT NULL constraints 
E141Basic integrity constraints3PRIMARY KEY constraints 
E141Basic integrity constraints8NOT NULL inferred on PRIMARY KEY 
E161SQL comments using leading double minus   
F021Basic information schema1COLUMNS view 
F021Basic information schema2TABLES view 
F021Basic information schema3VIEWS view 
F021Basic information schema4TABLE_CONSTRAINTS view 
F021Basic information schema5REFERENTIAL_CONSTRAINTS view 
F021Basic information schema6CHECK_CONSTRAINTS view 
F031Basic schema manipulation1CREATE TABLE statement to create persistent base tables 
F031Basic schema manipulation4ALTER TABLE statement: ADD COLUMN clause 
F034Extended REVOKE statement   
F034Extended REVOKE statement1REVOKE statement performed by other than the owner of a schema object 
F041Basic joined table1Inner join (but not necessarily the INNER keyword) 
F041Basic joined table2INNER keyword 
F041Basic joined table3LEFT OUTER JOIN 
F041Basic joined table4RIGHT OUTER JOIN 
F041Basic joined table5Outer joins can be nested 
F041Basic joined table7The inner table in a left or right outer join can also be used in an inner join 
F041Basic joined table8All comparison operators are supported (rather than just =) 
F051Basic date and time3TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 
F051Basic date and time4Comparison predicate on DATE, TIME, and TIMESTAMP data types 
F051Basic date and time5Explicit CAST between datetime types and character string types 
F111Isolation levels other than SERIALIZABLE   
F131Grouped operations1WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views 
F131Grouped operations3Set functions supported in queries with grouped views 
F171Multiple schemas per user   
F201CAST function   
F261CASE expression   
F261CASE expression1Simple CASE 
F261CASE expression2Searched CASE 
F261CASE expression3NULLIF 
F261CASE expression4COALESCE 
F262Extended CASE expression   
F311Schema definition statement2CREATE TABLE for persistent base tables 
F391Long identifiers   
F401Extended joined table2FULL OUTER JOIN 
F401Extended joined table4CROSS JOIN 
F481Expanded NULL predicate   
F501Features and conformance views1SQL_FEATURES view 
F571Truth value tests   
F850Top-level <order by clause> in <query expression>   
F855Nested <order by clause> in <query expression>   
T031BOOLEAN data type   
T175Generated columns   
T321Basic SQL-invoked routines1User-defined functions with no overloading 
T321Basic SQL-invoked routines3Function invocation 
T321Basic SQL-invoked routines6ROUTINES view 
T631IN predicate with one list element