Version 4.7.0

Released on 2022-01-25.


If you are upgrading a cluster, you must be running CrateDB 4.0.2 or higher before you upgrade to 4.7.0.

We recommend that you upgrade to the latest 4.3 release before moving to 4.7.0.

A rolling upgrade from 4.6.x to 4.7.0 is supported.

Before upgrading, you should back up your data.

Table of Contents



SQL Statements and Compatibility

  • Added support for the END statement for improved PostgreSQL compatibility.

  • Added support to use an aggregation in an order-by clause without having them in the select list like select x from tbl group by x order by count(y)

  • Changed the type precedence rules for INSERT FROM VALUES statements. The target column types now take higher precedence to avoid errors in statements like INSERT INTO tbl (text_column) VALUES ('a'), (3). Here 3 (INTEGER) used to take precedence, leading to a cast error because a cannot be converted to an INTEGER.

    This doesn’t change the behavior of standalone VALUES statements. VALUES ('a'), (3) as a standalone statement will still fail.

  • Introduced RESPECT NULLS and IGNORE NULLS flags to window function calls. The following window functions can now utilize the flags: LEAD, LAG, NTH_VALUE, FIRST_VALUE, and LAST_VALUE.

  • Added FAIL_FAST option to COPY FROM statement that when it is set to true, any errors observed while processing the statement will trigger an exception and the on-going executions will terminate in best effort.

Scalar and Aggregation Functions

  • Added the scalar function array_append which adds a value at the end of an array

  • Registered the scalar function array_to_string under the pg_catalog schema.

  • Added the scalar function pg_encoding_to_char which converts an PostgreSQL encoding’s internal identifier to a human-readable name.

  • Added the scalar function age which returns interval between 2 timestamps.

  • Added the date_bin scalar function that truncates timestamp into specified interval aligned with specified origin.

  • Added the array_slice(anyarray, from, to) scalar function.

  • Added support for the array slice access expression anyarray[from:to].

  • Added support of numeric type to the avg aggregation function.

  • Added the area(geo_shape) scalar function that calculates the area for a GEO_SHAPE.

  • Enabled the setting of most prototype methods for JavaScript Objects (e.g. Array.prototype, Object.prototype) in user-defined functions

New Tables and Schema Extensions

  • Added an empty pg_catalog.pg_locks table for improved PostgreSQL compatibility.

  • Added an empty pg_catalog.pg_indexes table for compatibility with PostgreSQL.

  • Added a new table_partitions column to the sys.snapshots table.

  • Added the column_details column to the information_schema.columns table including the top level column name and path information of object elements.

Administration and Operations

  • Added a sys node check for max shards per node to verify that the amount of shards on the current node is less than 90 % of cluster.max_shards_per_node. The check is exposed via sys.node_checks.

  • Added error_on_unknown_object_key session setting. This will either allow or suppress an error when unknown object keys are queried from dynamic objects.

  • Enabled HTTP connections to preserve session settings across the requests as long as the connection is re-used.

    Note that connections are established on an individual node to node basis. If a client sends requests to different nodes, those won’t share the same session settings, unless the client sets the session settings on each node individually.

  • Improved the visual layout of the administration console: Remove dedicated “Monitoring” page and move its contents to the “Overview” page.

New Types

  • Added float4 type as alias to real and float8 type as alias to double precision

  • Added the JSON type.


  • Improved optimizer rewrite rules for outer join to inner joins rewrites. Previously using aliases could prevent the rewrite from working.