Supported Column Types

The following PostgreSQL column types are supported:

• bigint.
• integer.
• smallint.
• real.
• double precision.
• boolean.
• timestamp with time zone.
• timestamp without time zone.
• date.
• time without time zone.
• uuid.
• text.
• varchar(N).
• char(N).
• bytea.
• numeric(P,S) (P ≤ 38).
• jsonb / json.
• interval.
• vector(N) / halfvec(N) (pgvector; see Working with Embeddings).
• one-dimensional arrays of most of these types (see Supported Array Types).

Other data types (unbounded numeric, xml, tsvector, range/multirange types, custom enums, composite types, pgvector's sparsevec, inet, cidr, oid, and the arrays that Supported Array Types lists as refused at registration) are rejected when a tiered table is registered, and when a decoupled table is created. ColdFront refuses silent fallback to varchar - losing precision/identity is worse than no support. inet/cidr/oid are rejected because pg_duckdb cannot process them in any Iceberg-backed query, so no cast makes them readable; store IP data as text and oid values as bigint instead (you can still index/compare them; cast on the hot side only if needed).

A few supported types need a closer look:

• Iceberg cannot hold a numeric NaN, or an infinity or -infinity value in a date, timestamp with time zone or timestamp without time zone column, as array elements too. ColdFront refuses those values with a check constraint named coldfront_guard_<column>; a write that goes to the cold tier of a tiered or decoupled table is refused by DuckDB, unless the write reads a numeric NaN from a PostgreSQL table and stores it as 0.
• char(N) is stored and read as varchar. Values, comparisons, and length() round-trip losslessly, but a cold read comes back unpadded with pg_typeof varchar, because pg_duckdb has no fixed-length char type. Use text or varchar if blank-padded display matters.
• json and jsonb are stored as varchar in Iceberg (no native primitive); reads come back as DuckDB's json, not the rich PG jsonb type, because Iceberg-backed reads run entirely in DuckDB.
• interval is stored as varchar in Iceberg, as <months> months <days> days <seconds> seconds, the text PostgreSQL and DuckDB both read as the same value; it is view-cast back to the rich PG type on read.

The archiver adds the NaN/infinity constraints at the first archive pass that tiers a partition of the table, and validates them once that pass's bootstrap transaction has committed. A value written before then is accepted, and each archive pass stops with an error that names the constraint until the row is fixed; the next pass then validates the constraint and tiers the table. A column that an ALTER TABLE adds through the DDL hook gets its constraint in the same statement, which PostgreSQL validates against every row of the hot table, so an ADD COLUMN whose default the constraint refuses, such as DEFAULT 'NaN', fails. A renamed column's constraint takes the new column name, and a column whose name is longer than 47 bytes gets a constraint named after a hash of the name.

A cold UPDATE ... FROM or DELETE ... USING, a MERGE whose source is a PostgreSQL table, and an INSERT ... SELECT into a decoupled table read that PostgreSQL table through DuckDB's postgres extension, which reads a numeric NaN as 0, also inside an array, and drops an array's lower bound. ColdFront cannot refuse those values on that path: the write stores the changed values with no error, and its conditions compare the changed values. A condition that names 'NaN' fails in such a write, because DuckDB does not accept 'NaN' as a numeric value. To keep those values out of the cold tier, check the source table in PostgreSQL before the write, and fix or remove the rows that hold them.

The following query counts those rows in a source table staging with a numeric column amount, a numeric array amounts and an array tags:

SELECT count(*)
  FROM staging
 WHERE amount = 'NaN'
    OR 'NaN' = ANY (amounts)
    OR array_lower(tags, 1) <> 1;

List every numeric column, numeric array and array column of your source table the same way; a count of 0 means that the write stores those columns unchanged.

Queries like data->>'key' and data->'key' work against jsonb, and ColdFront translates the ::jsonb cast, jsonb_array_length, jsonb_build_object / jsonb_agg (and their json_ twins, which DuckDB also lacks) and date_bin on read: the builders become the concat / to_json / array_agg form both engines evaluate identically (the result is JSON-equal to jsonb's rendering, in compact form and in argument order), and date_bin becomes DuckDB's time_bucket, which takes the same arguments and agrees on every fixed-width bucket.

ColdFront also rewrites the JSON operators that DuckDB lacks into DuckDB's JSON functions, which return PostgreSQL's answers. The rewrite covers:

• the #>, #>>, ?, ?|, ?&, @> and <@ operators.
• the json_extract_path and jsonb_extract_path functions and their _text forms.
• the jsonb_exists, jsonb_exists_any and jsonb_exists_all functions.
• the jsonb_contains and jsonb_contained functions.

A path or key for these operators and functions must be a constant or a bound parameter, not a column. A path can hold at most eight negative, signed or zero-padded index elements, such as -1, +1 or 01. PostgreSQL has no json form of ?, ?|, ?&, @> and <@, only a jsonb one. Cast the view's json column to jsonb first, as in data::jsonb ? 'k'. Two kinds of result differ from PostgreSQL's. DuckDB writes a returned container or number in its own form, such as {"b":"c"}, or 1.5 for 1.50. The ->> operator returns values in the same form. The @> and <@ operators never match an integer against the same value written as a decimal. For example, a stored {"p": 10.0} does not contain {"p": 10}, because DuckDB compares JSON numbers by kind. ColdFront does not support most other jsonb functions on tiered or decoupled data, such as jsonb_typeof, jsonb_set, jsonb_path_*, jsonb_each and jsonb_object_keys. DuckDB either lacks these functions, or its function of the same name differs in signature or result.

These limits apply only to reads that go through a tiered or iceberg-only view, which pg_duckdb plans entirely in DuckDB. Ordinary (non-tiered) PostgreSQL tables are untouched by ColdFront and keep full jsonb support, as does the hot partition table itself.

Bound parameters ($1 from a prepared statement, a driver's extended protocol, or a plpgsql variable) work in such reads. DuckDB types most placeholders from their context (ts > $1); one that is a direct argument of a DuckDB function with several overloads (time_bucket's origin, so date_bin's) or any argument of a table function (generate_series) it cannot, so ColdFront plans such a read from the bound values on every execution instead of caching a generic plan. The rewrite of #>, #>>, ?, ?|, ?&, @> and <@ also needs the value of any parameter in an operand. ColdFront plans such a read from the bound values on every execution as well. Under plan_cache_mode = force_generic_plan those reads cannot run: the forced generic plan has no values and fails with only works with DuckDB execution.

A hot-only read is the exception. When a SELECT reads a tiered view directly (no join, CTE, or sub-query) and its WHERE provably restricts to the hot tier, ColdFront rewrites it to read the hot partition table in plain PostgreSQL: the full jsonb operator and function set works, and the query skips DuckDB entirely. Such a read returns data as native jsonb rather than the view's json. Reads that span tiers, are cold-only, or reach the view through a join or sub-query stay in DuckDB, where the limits above apply.

Supported Array Types

A one-dimensional array of a supported type tiers as an Iceberg list of that type. The following table shows how each array type is stored and how the view reads it back:

The following arrays are refused when a tiered table is registered and when a decoupled table is created, with an error that names the column:

• timestamptz[], because pg_duckdb cannot read a timestamptz[] column of a PostgreSQL table, which a tiered table's hot tier is; store timestamp[] in UTC instead.
• jsonb[], json[] and interval[], because those types are stored as text and read back through a cast that an array element does not get.
• arrays of vector or halfvec, because a vector is itself stored as a list, and ColdFront stores an array as a list of scalar values.

An Iceberg list has one dimension and numbers its elements from 1, so ColdFront refuses an array with more than one dimension or with a lower bound other than 1, such as '[0:2]={1,2,3}'. The hot table's check constraint refuses such an array when it is written. A write to the cold tier refuses it too: a tiered INSERT with an error that names the column, and a bound parameter with an error that names no column. A write that reads the array from a PostgreSQL table stores it renumbered from 1 instead. A tiered table with a column declared with more than one dimension, such as integer[][], passes registration and is refused at its first archive pass, because pg_duckdb reads a column by its declared dimensions; a decoupled table refuses such a column when it is created. A decoupled table's array type is spelled as its element type followed by [], such as integer[]; the spellings integer[3] and integer ARRAY are refused. ALTER TABLE ... ADD COLUMN cannot add an array column to a tiered table, because duckdb-iceberg does not add a nested column to an Iceberg table.

A tiered INSERT accepts an array in any spelling, because its source runs in PostgreSQL. A cold UPDATE and a decoupled INSERT run in DuckDB, which does not read PostgreSQL's brace literal: a '{a,b}' literal reaches DuckDB as text and fails to cast. Write the array as ARRAY['a', 'b'], or pass it as a bound parameter.

Reads through a view run in DuckDB once the table has a cold tier, and the following array operations work there:

• x = ANY(arr), arr @> ARRAY[...] and arr && ARRAY[...].
• subscripts and slices, which count from 1, such as arr[1] and arr[1:2].
• array_length(arr, 1) and unnest(arr).

A brace literal and a bound array parameter fail in such a read, as they do for a vector, and so does cardinality(), which DuckDB defines for maps only.