SQL compatibility

CrateDB provides a standards-based SQL implementation similar to many other SQL databases. In particular, CrateDB aims for compatibility with PostgreSQL. However, CrateDB’s SQL dialect does have some unique characteristics, documented on this page.

See also

SQL: Syntax reference

Table of contents

Implementation notes

Data types

CrateDB supports a set of primitive data types. The following table defines how data types of standard SQL map to CrateDB Data types.

Standard SQL	CrateDB
integer	integer, int, int4
bit[8]	byte, char
boolean, bool	boolean
char [(n)], varchar [(n)]	string, text, varchar, character varying
timestamp with time zone	timestamp with time zone, timestamptz
timestamp	timestamp without time zone
smallint	short, int2, smallint
bigint	long, bigint, int8
real	float, real
double precision	double, double precision

Create table

CREATE TABLE supports additional storage and table parameters for sharding, replication and routing of data, and does not support inheritance.

Alter table

ALTER COLUMN action is 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, START, 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

  • WITH Queries (Common Table Expressions)

• Sequences

• Inheritance

• Constraints

  • Unique

  • Foreign key

  • Exclusion constraints

These functions of standard SQL 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 functions

• IS DISTINCT FROM

• Network address functions and operators

• Mathematical functions

  • Certain functions supported (see Mathematical functions)

• Set returning functions

• Trigger functions

• XML functions

Note

The currently supported and unsupported features in CrateDB are exposed in the Information schema table (see sql_features for usage).

CrateDB also supports the PostgreSQL wire protocol.

If you have use cases for any missing features, functions, or dialect improvements, let us know on GitHub! We are always improving and extending CrateDB and would love to hear your feedback.