Native SQL syntax

CrateDB offers a standards-based SQL interface that will feel familiar to users of other relational databases. While CrateDB aims for compatibility with PostgreSQL, its SQL dialect includes some unique characteristics optimized for distributed and analytical use cases.

This page provides a high-level overview of CrateDB’s SQL implementation, highlighting supported features, deviations from standard SQL, and current limitations.

See Also

Table of Contents

Implementation Notes

CrateDB’s SQL engine is designed for flexibility, scalability, and performance in distributed environments. While it shares many behaviors with PostgreSQL, it diverges in key areas to support features like sharding, replication, and eventual consistency.

CrateDB also supports the PostgreSQL wire protocol, making it compatible with many PostgreSQL client tools and libraries.

Data Types

CrateDB supports a broad range of primitive data types. The table below summarizes how standard SQL types map to CrateDB equivalents:

Standard SQL
CrateDB Equivalent

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

For complex types (arrays, objects, geospatial types, etc.), see the SQL Reference.

CREATE TABLE

The CREATE TABLE statement in CrateDB supports additional parameters specific to distributed systems, such as:

  • Sharding: Control how data is distributed across nodes

  • Replication: Specify the number of data copies

  • Routing: Define how records are assigned to shards

CrateDB does not support table inheritance (INHERITS clause).

See the CREATE TABLE documentation for full syntax and examples.

ALTER TABLE

CrateDB supports many ALTER TABLE operations, but note that ALTER COLUMN is not currently supported.

For a full list of supported actions, refer to the ALTER TABLE documentation.

System Information Tables

CrateDB provides read-only system tables and information schema tables that expose metadata and real-time cluster statistics.

While their schemas differ slightly from standard SQL definitions, they offer valuable insights into:

  • Cluster topology

  • Shard distribution

  • Node performance

  • SQL feature support

See System Information Tables for more details.

BLOB Support

In standard SQL, binary data is typically stored using BLOB or BYTEA types. In CrateDB, binary data is managed separately using BLOB Tables, which are:

  • Fully sharded and replicated

  • Designed for efficient storage and retrieval of large files

For more details, see Working with Blobs.

Transactions (BEGIN, START, COMMIT, and ROLLBACK)

CrateDB is optimized for analytical and high-throughput use cases rather than traditional transactional workloads. As such:

  • It does not support multi-statement transactions.

  • Each statement is executed and committed immediately.

  • Replication across the cluster happens automatically and asynchronously.

That said, CrateDB includes a row-level versioning system, which can be used to implement techniques like Optimistic Concurrency Control (OCC)—a common strategy for ensuring data integrity in distributed systems.

Unsupported Features and Functions

CrateDB intentionally omits or only partially supports certain standard SQL features that are less relevant in its distributed, analytical context.

Unsupported SQL Features

  • Stored procedures

  • Triggers

  • Sequences

  • Table inheritance

  • Constraints:

    • UNIQUE

    • FOREIGN KEY

    • EXCLUSION

Partially Supported or Unsupported SQL Functions

  • Aggregate functions: Many are supported — see Aggregation

  • Window functions: Several are available — see Window Functions

  • ENUM support functions

  • Network address functions

  • Set-returning functions

  • Trigger functions

  • XML functions

  • Mathematical functions: Some supported — see Math Functions

The sql_features system table provides a list of supported and unsupported features in your CrateDB version.

Share Feedback

CrateDB is continuously evolving. If you rely on a specific SQL feature or function that isn’t yet supported, we want to hear from you!

👉 Submit feedback or feature requests via GitHub.

PreviousQueryingNextJoins

Last updated