CrateDB Blog | Development, integrations, IoT, & more

CrateDB v5.0: Storage, SQL Compatibility, and Scalar Functions

Written by Jan Weber | 2022-07-19

CrateDB v5.0 is now ready to use!

Our team just released CrateDB v5.0! We're updating our storage engine to the latest version and bringing several improvements and enhancements to SQL compatibility, PostgreSQL inter-op, and scalar functions to CrateDB.

Let's take a quick tour of what is new.

At a glance:

  • Updated storage engine
  • SQL compatibility
  • Support for WITH queries
  • Added FETCH clause as an alternative to LIMIT
  • TIMESTAMP data type equivalent to TIMESTAMP WITHOUT TIMESTAMP
  • New scalar functions
  • Concat_ws to allow custom separators
  • Object_keys to retrieve first-level keys of object columns
  • Continue below for more details, or find the full release notes here 

Upgrade Storage Engine

CrateDB 5.0 comes with the latest version of Lucene as our storage backend, with all benefits the updated storage engine delivers. With these changes, there is something to consider before updating.

Due to incompatibilities, the database tables created with CrateDB 3.x or earlier can not be automatically migrated and need to be re-created, you can check the documentation here.

The latest version of Lucene no longer supports the simple storage type, in case you are using it today check the documentation for support storage types.

SQL compatibility

CrateDB now supports WITH queries to be used as a kind of temporary table in larger queries to simplify the readability of queries. Currently, only non-recursive WITH queries are supported.

Besides WITH queries, there have also been changes to limiting the result set of a query, the LIMIT and OFFSET clauses can now be declared in any order. We have also added the FETCH clause as an alternative to the LIMIT clause.

Scalar functions

We have added the concat_ws scalar function, which allows for concatenation with custom separators. The scalar function has_schema_privilege allows checking if a user has the necessary privileges for the schema, which is important for many 3rd party tools.

The newly added object_keys scalar function returns the first-level set of keys of an object, simplifying object handling in queries and code.

Read the full release notes here.

Stay up to date with our product and team news on LinkedIn and Twitter


Do you have questions? Don't hesitate to contact our team!