Feedback
3.3
Compatibility¶
CrateDB aims to provide an SQL implementation that is familiar to anyone having used other databases providing a standards-compliant SQL language. However, it is worth being aware of some unique characteristics in CrateDB’s SQL dialect.
Table of Contents
Implementation Notes¶
Data Types¶
CrateDB supports a set of primitive data types: integer, long, short, double, float, and byte, with the same ranges as corresponding Java types.
The following table defines how data types of standard SQL map to CrateDB Data Types.
Standard SQL |
CrateDB |
---|---|
integer |
integer, int |
bit[8] |
byte |
boolean, bool |
boolean, bool |
char [(n)], varchar [(n)] |
string |
timestamp |
timestamp |
smallint |
short |
bigint |
long |
real |
float |
double precision |
double |
Create Table¶
CREATE TABLE supports additional storage and table parameters for sharding, replication and routing of the data, and does not support inheritance.
Alter Table¶
ALTER COLUMN
and DROP COLUMN
actions are not currently supported (see
ALTER TABLE).
System Information Tables¶
The read-only System Information and Information Schema tables have a slightly different schema than specified in standard SQL. They provide schema information and can be queried to get real-time statistical data about the cluster, its nodes, and their shards.
BLOB Support¶
Standard SQL defines a binary string type, called BLOB
or BINARY LARGE
OBJECT
. With CrateDB, Binary Data is instead stored in separate BLOB Tables
(see Blobs) which can be sharded and replicated.
Transactions (BEGIN
, COMMIT
, and ROLLBACK
)¶
CrateDB is focused on providing analytical capabilities over supporting traditional transactional use cases, and thus it does not provide transaction control. Every statement commits immediately and is replicated within the cluster.
However, every row in CrateDB has a version number that is incremented whenever the record is modified. This version number can be used to implement patterns like Optimistic Concurrency Control, which can be used to solve many of the use cases that would otherwise require traditional transactions.
Unsupported Features and Functions¶
These features of standard SQL are not supported:
Stored Procedures
Triggers
VALUES
list used as constant tablesWITH
StatementsSequences
Inheritance
Constraints
Unique
Foreign key
Check constraints
Exclusion constraints
These functions are either not supported or only partly supported:
Aggregate functions
Various functions available (see Aggregation)
Window Functions
Various functions available (see Window Functions)
ENUM
support functionsIS DISTINCT FROM
Network address functions and operators
Mathematical functions
Certain functions supported (see Mathematical Functions)
Set returning functions
Trigger functions
XML functions
The currently supported and unsupported features in CrateDB are exposed in the Information Schema table (see sql_features for usage).
If you are missing features, functions or dialect improvements and have a great use case for it, let us know on Github. We’re always improving and extending CrateDB, and we love to hear feedback.