CrateDB SQL and PostgreSQL

CrateDB uses the PostgreSQL Wire Protocol (since CrateDB 0.56), which makes it easy to use many PostgreSQL compatible tools and libraries directly with CrateDB. However, many of these tools assume that they are talking to PostgreSQL specifically, and thus rely on SQL extensions and idioms that are unique to PostgreSQL. Because of this, some tools or libraries may not work with other SQL databases such as CrateDB.

CrateDB’s SQL query engine enables real-time search & aggregations for online analytic processing (OLAP) and business intelligence (BI) with the benefit of the ability to scale horizontally. The use-cases of CrateDB are different than those of PostgreSQL, as CrateDB’s specialized storage schema and query execution engine address different requirements (see High Level Architecture).

The listed features below cover the main differences in implementation and dialect between CrateDB and PostgreSQL. A detailed comparison between CrateDB’s SQL dialect and standard SQL is defined in Standard SQL compliance.

COPY

CrateDB does not support the distinct sub-protocol that is used to serve COPY operations and provides another implementation for transferring bulk data using the COPY FROM and COPY TO statements.

Objects

The definition of structured values by using JSON types, composite types or HSTORE are not supported. CrateDB alternatively allows the definition of nested documents (of type object) that store fieldscontaining any CrateDB supported data type, including nested object types.

Type Casts

CrateDB accepts the Type Conversion syntax for conversion of one data type to another but does not support the PostgreSQL specific expression::type syntax (see Value Expressions).

Arrays

Declaration of Arrays

The definition of an array by writing its values as a literal constant with the syntax of '{ val1 delim val2 delim ... }' is not supported.

While multidimensional arrays in PostgreSQL must have matching extends for each dimension, CrateDB allows different length nested arrays as this example shows:

cr> select [[1,2,3],[1,2]] from sys.cluster;
+---------------------+
| [[1,2,3],[1,2]]     |
+---------------------+
| [[1, 2, 3], [1, 2]] |
+---------------------+
SELECT 1 row in set (0.003 sec)

Accessing Arrays

Fetching arbitrary rectangular slices of an array using lower-bound:upper-bound expression (see Arrays) in the array subscript is not supported.

Text Search Functions and Operators

The functions and operators provided by PostgreSQL for full-text search (see PostgreSQL Fulltext Search) are not compatible with those provided by CrateDB. For more information about the built-in full-text search in CrateDB refer to Fulltext Search.

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.

Expression Evaluation

Unlike PostgreSQL, expressions are not evaluated if the query results in 0 rows either because of the table is empty or by a not matching where clause.