Complex types

DEFAULT and SET USING

Columns of scalar types in tables that also contain columns of complex types can now use the DEFAULT and SET USING options. See CREATE TABLE and ALTER TABLE.

Columns of complex types are still restricted from using these options.

Parquet loose schema matching

The PARQUET parser do_soft_schema_match_by_name option now supports complex types.

Joins for EXPLODE and UNNEST

You can use the output of EXPLODE and UNNEST as if it were a relation in a query. In addition to CROSS JOIN, you can now use LEFT JOIN to include NULL results, as in the following example:

=> ALTER SESSION SET UDPARAMETER FOR ComplexTypesLib skip_partitioning = true;

=> SELECT student, MIN(score), AVG(score) FROM tests
LEFT JOIN LATERAL EXPLODE(scores) AS t (pos, score)
GROUP BY student;
 student | MIN |       AVG
---------+-----+------------------
 Bob     |  78 |               83
 Lee     |     |
 Pat     |     |
 Sam     |  85 | 93.3333333333333
 Tom     |  68 |               79
(5 rows)

The LATERAL keyword is required with LEFT JOIN. It is optional for CROSS JOIN.