Expression Partitioning

Constraints

• A maximum of four partition columns are supported.
• Void expressions are not supported.
• Complex data types are unsupported for all partition expressions.
• Truncate and bucket expressions do not support binary data.
• The INSERT INTO PARTITION function only works with identity expressions; other expressions are unsupported.
• Iceberg constraint: A table column cannot appear in both year/month/day/hour time expressions simultaneously. However, it can coexist in time and bucket/truncate expressions.
• Iceberg constraint: Partition keys follow default naming rules and must not conflict with existing column names in the table.
• Partition evolution functionality is currently unavailable.

Supported Partition Expressions

Fabric SQL supports the following partition expressions and their corresponding data types:

All expressions return null when processing null inputs.

The identity expression outputs the input value without modification.

The year expression calculates the number of years since 1970. The month expression calculates the number of months since January 1, 1970. The day expression calculates the number of days since January 1, 1970. The hour expression calculates the number of hours since January 1, 1970, at 00:00:00. When recording partition values, Iceberg converts integer results into human-readable time strings. For example, instead of storing the integer 55, Iceberg records the result of year('2025-10-10') as the string '2025'.

Fabric SQL offers SQL functions fq_iceberg_year(), fq_iceberg_month(), fq_iceberg_day(), fq_iceberg_hour(), fq_iceberg_truncate(), and fq_iceberg_bucket() to implement the calculation functionality of the corresponding partition expressions year, month, day, hour, truncate, and bucket.

Partition Expression: truncate

The truncate expression behaves differently depending on the data type involved. To compute its value, you can use the function fq_iceberg_truncate().

For integer types, truncate(W, integer_value) returns the largest number that does not exceed integer_value and is a multiple of W. Here is an example:

fabricsql=>select fq_iceberg_truncate (10, -2) ;
 fq_iceberg_truncate
---------------------
                 -10
(1 row)

fabricsql=> select fq_iceberg_truncate (10, 0) ;
 fq_iceberg_truncate
---------------------
                   0
(1 row)

fabricsql=> select fq_iceberg_truncate (10, 9) ;
 fq_iceberg_truncate
---------------------
                   0
(1 row)

fabricsql=> select fq_iceberg_truncate (10, 10) ;
 fq_iceberg_truncate
---------------------
                  10
(1 row)

fabricsql=> select fq_iceberg_truncate (10, 19) ;
 fq_iceberg_truncate
---------------------
                  10
(1 row)

For string types, truncate(W, string_value) returns the prefix substring of string_value with a length of W characters. Note that the length is measured in characters. Here is an example:

fabricsql=> select *, length(t)  from fq_iceberg_truncate (2, '') as t;
 iceberg_truncate | length
------------------+--------
                  |      0
(1 row)

fabricsql=> select *, length(t)  from fq_iceberg_truncate (2, 'a') as t;
 iceberg_truncate | length
------------------+--------
 a                |      1
(1 row)

fabricsql=> select *, length(t)  from fq_iceberg_truncate (2, 'tt') as t;
 iceberg_truncate | length
------------------+--------
 tt               |      2
(1 row)

fabricsql=> select *, length(t)  from fq_iceberg_truncate (2, 'cow') as t;
 iceberg_truncate | length
------------------+--------
 co               |      2
(1 row)

fabricsql=> select *, length(t)  from fq_iceberg_truncate (2, 'Hello, China!') as t;
 iceberg_truncate | length
------------------+--------
 Hello             |      2
(1 row)

For numeric types, truncate(W, num_value) returns the largest number that does not exceed num_value, where shifting the decimal point to the right by scale positions results in a multiple of W. Here is an example:

fabricsql=> select fq_iceberg_truncate (50, 10.64::numeric(15,3));
 fq_iceberg_truncate
---------------------
              10.600
(1 row)

fabricsql=> select fq_iceberg_truncate (50, 10.66::numeric(15,3));
 fq_iceberg_truncate
---------------------
              10.650
(1 row)

fabricsql=> select fq_iceberg_truncate (50, -10.66::numeric(15,3));
 fq_iceberg_truncate
---------------------
             -10.700
(1 row)

Creating an Expression-Partitioned Table

For detailed CREATE TABLE syntax, refer to CREATE TABLE.

Below is an example SQL statement for creating an expression-partitioned Iceberg-managed table:

fabricsql=> set current_schema to test;
fabricsql=> CREATE TABLE iceberg_table(id bigint, data text, category text, ts timestamp, val numeric) partition by (bucket(16, val), truncate(10, id), c3 int comment 'this is c3 field', year(ts) ) store as iceberg;
CREATE TABLE
fabricsql=> select * from pg_get_partition_keys('test', 'iceberg_table');
     table     |  partition_key
---------------+-----------------
 iceberg_table | bucket(16,val)
 iceberg_table | truncate(10,id)
 iceberg_table | c3
 iceberg_table | year(ts)
(4 rows)

In this example, c3 serves as an identity partition key. Additionally, three more partition keys are created using bucket, truncate, and year expressions. Iceberg automatically assigns internal names to these partition keys based on the rules outlined in the table below.

Column names must not conflict with partition key names. Otherwise, the table creation will fail with an error.

fabricsql=> CREATE TABLE iceberg_table(id bigint, data text, category text, ts timestamp, val numeric) partition by (bucket(16, val),  c3 int comment 'this is c3 field', year(ts), ts_year bigint ) store as iceberg;
ERROR:  column "ts_year" specified more than once

Additionally, Iceberg does not allow a time-type column to be used simultaneously in multiple expressions such as year, month, day, and hour. However, it can still be used in other expressions like truncate or bucket.

fabricsql=> CREATE TABLE iceberg_table(id bigint, data text, category text, ts timestamp, val numeric) partition by (bucket(16, val),  c3 int comment 'this is c3 field', year(ts), month(ts) ) store as iceberg;
ERROR:  java.lang.IllegalArgumentException: Cannot add redundant partition: 1002: ts_year: year(4) conflicts with 1003: ts_month: month(4).

fabricsql=> CREATE TABLE iceberg_table(id bigint, data text, category text, ts timestamp, val numeric) partition by (bucket(16, val),  c3 int comment 'this is c3 field', year(ts), bucket(7, ts) ) store as iceberg;
CREATE TABLE

A maximum of four partition keys can be created in Fabric SQL. Otherwise, an error is reported and the table fails to be created.

fabricsql=> CREATE TABLE iceberg_table(id bigint, data text, category text, ts timestamp, val numeric) partition by (bucket(16, val), truncate(10, id), c3 int comment 'this is c3 field', year(ts) ) store as iceberg;
ERROR:  partitioned table should have no more than 4 partition keys

Fabric SQL does not support complex types as partition keys. Otherwise, an error is reported and the table creation fails.

fabricsql=> CREATE TABLE iceberg_table(id bigint, data text, category text, ts timestamp, val numeric) partition by (bucket(16, val), truncate(10, id), c3 struct<c: text, d: int>, year(ts) ) store as iceberg;
ERROR:  Complex type cannot be served as a value-partitioning column.

fabricsql=> CREATE TABLE iceberg_table(a int , b struct<c: text, d: int>) PARTITION BY(bucket(5, b.c), truncate(3, b.d)) store as ICEBERG;
ERROR:  syntax error at or near "."
LINE 1: ... struct<c: text, d: int>) PARTITION BY(bucket(5, b.c), trunc...
                                                             ^

Fabric SQL also supports creating expression-partitioned external Iceberg tables, but additional conditions must be met:

• The number of partition keys must match those of the Iceberg table stored in OBS.
• The definition of each partition key—including its order, expression, and associated column—must exactly align with the corresponding Iceberg table in OBS.

The following sections will use the iceberg_test_part_expr table as an example, defined as follows:

fabricsql=> set current_schema to test;
fabricsql=> CREATE TABLE iceberg_test_part_expr(id bigint, data text, category text, ts date) partition by (c3 int, truncate(4, data), year(ts)) store as iceberg;
CREATE TABLE

Importing Data

An Iceberg table with expression partitioning supports both single-row insertion and batch data import.

fabricsql=> INSERT INTO iceberg_test_part_expr values(9, 'hellohello',   'shanxi', '2025-08-29', 1);
INSERT 1
fabricsql=> INSERT INTO iceberg_test_part_expr values(9, 'hellohello',   'shanxi', '2025-08-29', 1);
INSERT 1
fabricsql=> INSERT INTO iceberg_test_part_expr values(9, 'he',           'shanxi', '2024-09-29', 20);
INSERT 1
fabricsql=> INSERT INTO iceberg_test_part_expr values(9, 'helloworld',   'shanxi', '2023-06-29', 6);
INSERT 1
fabricsql=> INSERT INTO iceberg_test_part_expr values(9, 'hella',        'shanxi', '2022-05-29', 60);
INSERT 1
fabricsql=> INSERT INTO iceberg_test_part_expr values(9, 'zhangmingmin', 'shanxi', '2025-04-29', 100);
INSERT 1

For the INSERT INTO PARTITION feature, Fabric SQL only works with tables where all partition keys are of the identity type. Tables using other types of expressions for partitioning are not supported.

fabricsql=> INSERT INTO iceberg_test_part_expr PARTITION(c3=1, data='hell', ts='2025') values (9, 'hellohello',   'shanxi', '2025-08-29 01:05:59.550463') ;
ERROR:  INSERT INTO PARTITION clause only support identity partition.

To view all partitioned data in a table, you can use the function pg_get_partitions(), as shown below:

fabricsql=> select partition_name from pg_get_partitions('iceberg_test_part_expr');
      partition_name
--------------------------
 c3=1/data=hell/ts=2025
 c3=20/data=he/ts=2024
 c3=100/data=zhan/ts=2025
 c3=6/data=hell/ts=2023
 c3=60/data=hell/ts=2022
(5 rows)

Query Optimization Based on Column Expressions

With support for expression partitioning, Fabric SQL enables column expression pushdown to optimize queries through static partition pruning. Here is an example:

fabricsql=> explain(analyze, predicate_only on, costs off, timing off) select * from iceberg_test_part_expr where c3 = 1;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
  id |                                operation                                 | A-rows
 ----+--------------------------------------------------------------------------+--------
   1 | ->  Row Adapter                                                          |      2
   2 |    ->  Partitioned Vector Foreign Scan on iceberg_test_part_expr         |      2
   3 |       ->  Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr |      2

            Predicate Information (identified by plan id)
 --------------------------------------------------------------------
   2 --Partitioned Vector Foreign Scan on iceberg_test_part_expr
         Filter: (c3 = 1)
         Pushdown Predicate Filter: (c3 = 1)
         Server Type: lf
         DN read from: direct
   3 --Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr
         Filter: (c3 = 1)
         Pushdown Predicate Filter: (c3 = 1)
         Server Type: lf
         Pruning results: (Manifests total: 6, Manifests left: 2)
         DN read from: direct
(19 rows)

After pushing down the column expression c3 = 1, the number of scanned partitions is reduced from 6 to 2.

Query Optimization Based on SQL Functions

Following the implementation of expression partitioning, Fabric SQL supports pushdown optimization for whitelisted SQL functions. The currently supported SQL functions are listed below:

Examples of static partition pruning using string functions left() and substring():

fabricsql=> explain(analyze, predicate_only on, costs off, timing off) select * from iceberg_test_part_expr where substring(data, 1, 3) > 'hello';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
  id |                                operation                                 | A-rows
 ----+--------------------------------------------------------------------------+--------
   1 | ->  Row Adapter                                                          |      1
   2 |    ->  Partitioned Vector Foreign Scan on iceberg_test_part_expr         |      1
   3 |       ->  Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr |      1

            Predicate Information (identified by plan id)
 --------------------------------------------------------------------
   2 --Partitioned Vector Foreign Scan on iceberg_test_part_expr
         Filter: ("substring"(data, 1, 3) > 'hello'::text)
         Server Type: lf
         DN read from: direct
   3 --Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr
         Server Type: lf
         Pruning results: (Manifests total: 6, Manifests left: 1)
         DN read from: direct
(16 rows)

fabricsql=> explain(analyze, predicate_only on, costs off, timing off) select * from iceberg_test_part_expr where left(data, 5) < 'hello';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
  id |                                operation                                 | A-rows
 ----+--------------------------------------------------------------------------+--------
   1 | ->  Row Adapter                                                          |      2
   2 |    ->  Partitioned Vector Foreign Scan on iceberg_test_part_expr         |      2
   3 |       ->  Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr |      5

            Predicate Information (identified by plan id)
 --------------------------------------------------------------------
   2 --Partitioned Vector Foreign Scan on iceberg_test_part_expr
         Filter: ("left"(data, 5) < 'hello'::text)
         Server Type: lf
         DN read from: direct
   3 --Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr
         Server Type: lf
         Pruning results: (Manifests total: 6, Manifests left: 5)
         DN read from: direct
(16 rows)

When substring() is pushed down, the number of partitions decreases from 6 to 1. With left(), the count reduces from 6 to 5.

Similarly, examples using time-based functions like date_trunc():

fabricsql=> explain(analyze, predicate_only on, costs off, timing off) select * from iceberg_test_part_expr where date_trunc('year', ts) > '2023-12-30 00:00:00';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
  id |                                operation                                 | A-rows
 ----+--------------------------------------------------------------------------+--------
   1 | ->  Row Adapter                                                          |      4
   2 |    ->  Partitioned Vector Foreign Scan on iceberg_test_part_expr         |      4
   3 |       ->  Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr |      4

                            Predicate Information (identified by plan id)
 ---------------------------------------------------------------------------------------------------
   2 --Partitioned Vector Foreign Scan on iceberg_test_part_expr
         Filter: (date_trunc('year'::text, ts) > '2023-12-30 00:00:00'::timestamp without time zone)
         Server Type: lf
         DN read from: direct
   3 --Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr
         Server Type: lf
         Pruning results: (Manifests total: 6, Manifests left: 4)
         DN read from: direct
(16 rows)

fabricsql=> explain(analyze, predicate_only on, costs off, timing off) select * from iceberg_test_part_expr where year(ts) < 2024;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
  id |                                operation                                 | A-rows
 ----+--------------------------------------------------------------------------+--------
   1 | ->  Row Adapter                                                          |      2
   2 |    ->  Partitioned Vector Foreign Scan on iceberg_test_part_expr         |      2
   3 |       ->  Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr |      2

            Predicate Information (identified by plan id)
 --------------------------------------------------------------------
   2 --Partitioned Vector Foreign Scan on iceberg_test_part_expr
         Filter: (year(ts) < 2024)
         Server Type: lf
         DN read from: direct
   3 --Partitioned Vector Foreign Meta Scan on iceberg_test_part_expr
         Server Type: lf
         Pruning results: (Manifests total: 6, Manifests left: 2)
         DN read from: direct
(16 rows)

When date_trunc() is pushed down, the number of partitions decreases from 6 to 4. With year(), the count reduces from 6 to 2.