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.

ID Package # Description Comments
--- --- --- --- ---
E011 Numeric data types 1 INTEGER and SMALLINT data types
E011 Numeric data types 2 REAL, DOUBLE PRECISION, and FLOAT data types
E011 Numeric data types 3 DECIMAL and NUMERIC data types Not supported in DDL
E011 Numeric data types 4 Arithmetic operators
E011 Numeric data types 5 Numeric comparison
E011 Numeric data types 6 Implicit casting among the numeric data types
E021 Character string types 2 CHARACTER VARYING data type
E021 Character string types 3 Character literals Only simple ‘ quoting
E021 Character string types 4 CHARACTER_LENGTH function char_length only
E021 Character string types 5 OCTET_LENGTH function
E021 Character string types 7 Character concatenation
E021 Character string types 8 UPPER and LOWER functions
E021 Character string types 9 TRIM function
E021 Character string types 10 Implicit casting among the character string types just one type
E021 Character string types 12 Character comparison
E031 Identifiers
E031 Identifiers 1 Delimited identifiers
E031 Identifiers 2 Lower case identifiers
E031 Identifiers 3 Trailing underscore
E051 Basic query specification 1 SELECT DISTINCT
E051 Basic query specification 2 GROUP BY clause
E051 Basic query specification 4 GROUP BY can contain columns not in <select list>
E051 Basic query specification 5 Select list items can be renamed
E051 Basic query specification 6 HAVING clause
E051 Basic query specification 7 Qualified * in select list
E051 Basic query specification 8 Correlation names in the FROM clause
E051 Basic query specification 9 Rename columns in the FROM clause
E061 Basic predicates and search conditions 1 Comparison predicate
E061 Basic predicates and search conditions 2 BETWEEN predicate
E061 Basic predicates and search conditions 3 IN predicate with list of values
E061 Basic predicates and search conditions 4 LIKE predicate
E061 Basic predicates and search conditions 6 NULL predicate
E061 Basic predicates and search conditions 9 Subqueries in comparison predicate
E061 Basic predicates and search conditions 11 Subqueries in IN predicate
E061 Basic predicates and search conditions 12 Subqueries in quantified comparison predicate
E061 Basic predicates and search conditions 14 Search condition
E071 Basic query expressions 2 UNION ALL table operator
E081 Basic Privileges
E081 Basic Privileges 1 SELECT privilege
E081 Basic Privileges 2 DELETE privilege
E091 Set functions 1 AVG
E091 Set functions 2 COUNT
E091 Set functions 3 MAX
E091 Set functions 4 MIN
E091 Set functions 5 SUM
E091 Set functions 7 DISTINCT quantifier
E101 Basic data manipulation
E101 Basic data manipulation 1 INSERT statement
E101 Basic data manipulation 3 Searched UPDATE statement
E101 Basic data manipulation 4 Searched DELETE statement
E131 Null value support (nulls in lieu of values)
E141 Basic integrity constraints 1 NOT NULL constraints
E141 Basic integrity constraints 3 PRIMARY KEY constraints
E141 Basic integrity constraints 6 CHECK constraints
E141 Basic integrity constraints 7 Column defaults
E141 Basic integrity constraints 8 NOT NULL inferred on PRIMARY KEY
E151 Transaction support 1 COMMIT statement
E152 Basic SET TRANSACTION statement
E152 Basic SET TRANSACTION statement 1 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause Is ignored
E152 Basic SET TRANSACTION statement 2 SET TRANSACTION statement: READ ONLY and READ WRITE clauses Is ignored
E161 SQL comments using leading double minus
F021 Basic information schema 1 COLUMNS view
F021 Basic information schema 2 TABLES view
F021 Basic information schema 3 VIEWS view
F021 Basic information schema 4 TABLE_CONSTRAINTS view
F021 Basic information schema 5 REFERENTIAL_CONSTRAINTS view
F021 Basic information schema 6 CHECK_CONSTRAINTS view
F031 Basic schema manipulation 1 CREATE TABLE statement to create persistent base tables
F031 Basic schema manipulation 2 CREATE VIEW statement
F031 Basic schema manipulation 3 GRANT statement
F031 Basic schema manipulation 4 ALTER TABLE statement: ADD COLUMN clause
F034 Extended REVOKE statement
F034 Extended REVOKE statement 1 REVOKE statement performed by other than the owner of a schema object
F041 Basic joined table 1 Inner join (but not necessarily the INNER keyword)
F041 Basic joined table 2 INNER keyword
F041 Basic joined table 3 LEFT OUTER JOIN
F041 Basic joined table 4 RIGHT OUTER JOIN
F041 Basic joined table 5 Outer joins can be nested
F041 Basic joined table 7 The inner table in a left or right outer join can also be used in an inner join
F041 Basic joined table 8 All comparison operators are supported (rather than just =)
F051 Basic date and time 1 DATE data type (including support of DATE literal)
F051 Basic date and time 3 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6
F051 Basic date and time 4 Comparison predicate on DATE, TIME, and TIMESTAMP data types
F051 Basic date and time 5 Explicit CAST between datetime types and character string types
F051 Basic date and time 6 CURRENT_DATE
F052 Intervals and datetime arithmetic
F111 Isolation levels other than SERIALIZABLE
F111 Isolation levels other than SERIALIZABLE 1 READ UNCOMMITTED isolation level Is ignored
F111 Isolation levels other than SERIALIZABLE 2 READ COMMITTED isolation level Is ignored
F111 Isolation levels other than SERIALIZABLE 3 REPEATABLE READ isolation level Is ignored
F131 Grouped operations 1 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views
F131 Grouped operations 3 Set functions supported in queries with grouped views
F171 Multiple schemas per user
F201 CAST function
F221 Explicit defaults
F222 INSERT statement: DEFAULT VALUES clause
F261 CASE expression
F261 CASE expression 1 Simple CASE
F261 CASE expression 2 Searched CASE
F261 CASE expression 3 NULLIF
F261 CASE expression 4 COALESCE
F262 Extended CASE expression
F311 Schema definition statement 2 CREATE TABLE for persistent base tables
F391 Long identifiers
F401 Extended joined table 2 FULL OUTER JOIN
F401 Extended joined table 4 CROSS JOIN
F471 Scalar subquery values
F481 Expanded NULL predicate
F501 Features and conformance views 1 SQL_FEATURES view
F571 Truth value tests
F763 CURRENT_SCHEMA
F850 Top-level <order by clause> in <query expression>
F851 <order by clause> in subqueries
F855 Nested <order by clause> in <query expression>
S091 Basic array support special syntax
S091 Basic array support 1 Arrays of built-in data types special syntax
S098 ARRAY_AGG
T031 BOOLEAN data type
T051 Row types Limited to built-in table functions
T071 BIGINT data type
T175 Generated columns
T321 Basic SQL-invoked routines 1 User-defined functions with no overloading
T321 Basic SQL-invoked routines 3 Function invocation
T321 Basic SQL-invoked routines 6 ROUTINES view
T441 ABS and MOD functions
T461 Symmetric BETWEEN predicate
T471 Result sets return value
T615 LEAD and LAG functions
T617 FIRST_VALUE and LAST_VALUE function
T618 NTH_VALUE function
T631 IN predicate with one list element