R/duckdb_types_arrow.R

In duckdb: DBI Package for the DuckDB Database Management System

# Generated by scripts/types-rd.R from handbook/usage/arrow-types/README.md:
# edit the leaf and re-run the script, never this file.

#' DuckDB data types through Arrow
#'
#' @description
#' This page documents every DuckDB type as it crosses to R through Arrow and back:
#' the Arrow type the engine exports it as, what the two R readers make of that,
#' which Arrow type writes it again, and which R functions keep Arrow's types on the way in.
#' See [duckdb_types] for a description of the direct conversion to R vectors.
#'
#' @details
#' # The routes
#'
#' **Reading.**
#' Every function that returns Arrow hands out the engine's own export, under the connection's settings:
#' [dbGetQueryArrow()], [dbFetchArrow()] and [dbFetchArrowChunk()] after [dbSendQueryArrow()], [dbReadTableArrow()],
#' [duckdb_fetch_arrow()] and [duckdb_fetch_record_batch()] after `dbSendQuery(arrow = TRUE)`, and [arrow::to_arrow()].
#' No R vector exists until a reader converts the stream,
#' and the two readers differ, so each entry below names both:
#' nanoarrow's `as.data.frame()`, and arrow's `as.data.frame()` of [arrow::as_arrow_table()].
#'
#' **The export settings** are DuckDB's, set with `SET`, and each changes the Arrow type of some columns:
#'
#' * `arrow_lossless_conversion = true` exports each type that has no exact Arrow counterpart as an extension type that names it,
#'   so that DuckDB, or another Arrow consumer that knows the name, gets the type back.
#'   Where nanoarrow falls back to the storage of one, the entries below say so.
#' * `arrow_large_buffer_size = true` gives strings, binary data and lists 64-bit offsets,
#'   as `large_string`, `large_binary` and `large_list`, which both readers convert as they convert the others.
#' * `arrow_output_version`, `'1.0'` by default, gates the newer layouts.
#'   From `'1.4'`, binary data exports as `binary_view`,
#'   and `produce_arrow_string_view = true` and `arrow_output_list_view = true` take effect, as `string_view` and `list_view`;
#'   with an older version those two change nothing.
#'   From `'1.5'`, a `DECIMAL` up to width 9 exports as `decimal32` and up to width 18 as `decimal64`.
#'   nanoarrow converts `string_view` and `binary_view`.
#'
#' **Writing.**
#' Only the routes that let DuckDB scan the Arrow data keep its types:
#' [duckdb_register_arrow()], and [arrow::to_duckdb()], which calls it.
#' `duckdb_register_arrow()` takes whatever `arrow::Scanner$create()` scans.
#' Nothing is copied: the result is a view, and `CREATE TABLE ... AS SELECT * FROM` it writes a table.
#' A registered arrow `Table` is scanned by every query, and a registered `RecordBatchReader` is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#' Every other route converts through an R data frame, so a column lands as the type its R vector writes (see [duckdb_types]).
#' [dbWriteTableArrow()], [dbCreateTableArrow()] and [dbAppendTableArrow()] are DBI's defaults, which do that batch by batch.
#' [dbBindArrow()] converts the same way, then binds by position.
#'
#' # Reading
#'
#' ## Numbers
#'
#' * **`BOOLEAN`** exports as `bool` and reads as `logical`;
#'   with `arrow_lossless_conversion`, as `arrow.bool8`, which nanoarrow reads as the `integer` of its storage.
#' * **`TINYINT`, `SMALLINT`, `UTINYINT`, `USMALLINT`** export as `int8`, `int16`, `uint8` and `uint16`, and read as `integer`, exactly.
#' * **`INTEGER`** exports as `int32` and reads as `integer`, exactly but for the minimum.
#' * **`UINTEGER`** exports as `uint32`.
#'   nanoarrow reads it as `numeric`; arrow reads it as `integer` when every value fits, and as `numeric` otherwise.
#' * **`BIGINT`** exports as `int64`.
#'   nanoarrow reads it as `numeric`, exact up to 2^53.
#'   arrow reads it as `integer` when every value fits and as `bit64::integer64` otherwise, exactly but for the minimum,
#'   or as `integer64` always under `options(arrow.int64_downcast = FALSE)`.
#' * **`UBIGINT`** exports as `uint64`, and both read it as `numeric`, exact up to 2^53;
#'   its rounding past that is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#' * **`HUGEINT`, `UHUGEINT`** export as `decimal128(38, 0)`, and both read them as `numeric`;
#'   their rounding is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations), and so is a `UHUGEINT` of 2^127 or more reading as negative.
#'   With `arrow_lossless_conversion` they export as `arrow.opaque`, which carries every value.
#'   Their text reads them exactly (see [duckdb_types]).
#' * **`BIGNUM`** exports as `arrow.opaque` under either setting;
#'   nanoarrow reads its storage bytes as a `blob`.
#' * **`DECIMAL(width, scale)`** exports as `decimal128(width, scale)`, or narrower from output version 1.5,
#'   and both read it as `numeric`; its rounding is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#' * **`FLOAT`, `DOUBLE`** export as `float` and `double`, and read as `numeric`.
#'
#' ## Text and binary
#'
#' * **`VARCHAR`** exports as `string` and reads as `character`.
#' * **`BLOB`** exports as `binary`; nanoarrow reads it as a `blob::blob`, and arrow as an `arrow_binary` list of raw vectors.
#' * **`BIT`** exports as `binary`, the bytes DuckDB stores for the bit string, and reads as a `blob` or `arrow_binary` of those bytes.
#'   With `arrow_lossless_conversion` it exports as `arrow.opaque`, which nanoarrow reads as the same bytes.
#'   Its text reads it as `0` and `1` (see [duckdb_types]).
#' * **`UUID`** exports as `string`, lowercase and hyphenated, and reads as `character`.
#'   With `arrow_lossless_conversion` it exports as `arrow.uuid`.
#'
#' ## Dates and times
#'
#' * **`DATE`** exports as `date32` and reads as `Date`.
#' * **`TIME`** exports as `time64('us')` and reads as `hms`.
#' * **`TIME_NS`** exports as `time64('ns')` and reads as `hms`.
#' * **`TIMETZ`** exports as the `time64('us')` of its local time and reads as `hms`;
#'   the offset it drops is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#'   With `arrow_lossless_conversion` it exports as `arrow.opaque`, which keeps the offset.
#' * **`TIMESTAMP_S`, `TIMESTAMP_MS`, `TIMESTAMP`, `TIMESTAMP_NS`** export as `timestamp` in their own unit, without a zone,
#'   and read as `POSIXct`, whose double cannot hold a `TIMESTAMP_NS`'s nanoseconds, a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#'   The two readers label the same instant differently:
#'   nanoarrow gives it the zone `UTC`, so it prints the stored clock,
#'   and arrow gives it none, so it prints in R's session zone, a different clock outside UTC.
#' * **`TIMESTAMPTZ`** exports as `timestamp('us', zone)`, where the zone is DuckDB's `TimeZone` setting, and both readers read it as a `POSIXct` labelled with that zone.
#' * **`INTERVAL`** exports as `interval_month_day_nano`.
#'
#' ## Enums and nested types
#'
#' * **`ENUM`** exports as a dictionary of its values; nanoarrow reads it as `character`, and arrow as `factor`.
#' * **`ARRAY`** exports as a `fixed_size_list` and reads as a list of vectors ([vctrs::list_of()] or `arrow_fixed_size_list`),
#'   without the `array = "matrix"` that [dbGetQuery()] needs.
#' * **`LIST`** exports as `list` and reads as a list of vectors (`vctrs::list_of()` or `arrow_list`).
#' * **`MAP`** exports as `map` and reads as a list of key and value data frames.
#' * **`STRUCT`** exports as `struct` and reads as a data frame column, a tibble in arrow's case.
#' * **`UNION`** exports as a `sparse_union`.
#'   nanoarrow reads it as a data frame with a column per member, `NA` where the value is another member's.
#'
#' ## Everything else
#'
#' * **`NULL`**, untyped, exports as `int32` and reads as `NA_integer_`, as it does through `dbGetQuery()`.
#' * **`JSON`** exports as `string` and reads as `character`;
#'   with `arrow_lossless_conversion` it exports as `arrow.json`, which nanoarrow reads as `character`.
#' * **`INET`** exports as a struct whose `address` is the `HUGEINT` the engine stores, as a `decimal128(38, 0)`,
#'   which both read as a double, the same as `dbGetQuery()` does,
#'   and its text reads the address (see [duckdb_types]).
#'   With `arrow_lossless_conversion` that field becomes `arrow.opaque`.
#'
#' # Writing
#'
#' ## Arrow types
#'
#' Each Arrow type lands as one DuckDB type when DuckDB scans it:
#'
#' * **`bool`, `int8` to `int64`, `uint8` to `uint64`, `float`, `double`** land as `BOOLEAN`, the integer type of the same width and sign,
#'   `FLOAT` and `DOUBLE`.
#' * **`decimal32`, `decimal64`, `decimal128`** land as `DECIMAL` of the same width and scale.
#' * **`string`, `large_string`, `string_view`** land as `VARCHAR`,
#'   and **`binary`, `large_binary`, `binary_view`, `fixed_size_binary`** as `BLOB`.
#' * **`date32`, `date64`** land as `DATE`.
#' * **`time32` and `time64('us')`** land as `TIME`, and **`time64('ns')`** as `TIME_NS`.
#' * **`timestamp`** without a zone lands as `TIMESTAMP_S`, `TIMESTAMP_MS`, `TIMESTAMP` or `TIMESTAMP_NS` by its unit,
#'   and with a zone as `TIMESTAMPTZ`, the same instant.
#' * **`duration`** lands as `INTERVAL` in any unit.
#' * **`interval_months`** and **`interval_month_day_nano`** land as `INTERVAL`, each part kept.
#' * **`list`, `large_list`, `list_view`** land as `LIST`, and **`fixed_size_list`** as `ARRAY`.
#' * **`struct`** lands as `STRUCT`, **`map`** as `MAP`, and **`sparse_union`** as `UNION`.
#' * **A dictionary** lands as `VARCHAR`.
#' * **`na`** lands as a column of type `NULL`.
#' * **The extension types** land as the DuckDB type they name:
#'   `arrow.uuid` as `UUID`, `arrow.json` as `JSON`, `arrow.bool8` as `BOOLEAN`, and `arrow.opaque` as the DuckDB type in its metadata.
#'   What GeoArrow WKB lands as is under Geometry, and the other GeoArrow encodings are a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#'
#' ## R classes, through Arrow
#'
#' An R vector reaches DuckDB through Arrow as the Arrow type its package infers,
#' which differs from what [dbWriteTable()] gives for some classes.
#' Where nothing is said below, nanoarrow and arrow infer the type `dbWriteTable()` writes.
#'
#' * **`factor`** infers a dictionary and lands as `VARCHAR`, where `dbWriteTable()` writes `ENUM`.
#'   arrow lands an `ordered` one as `VARCHAR` too.
#' * **`POSIXct`** infers a `timestamp` with its zone, or R's session zone where it has none, and lands as `TIMESTAMPTZ`,
#'   where `dbWriteTable()` writes a plain `TIMESTAMP`.
#' * **`difftime`** infers a `duration` and lands as `INTERVAL` in hours and below, so 2 days land as `48:00:00`,
#'   where `dbWriteTable()` keeps the days.
#' * **`hms`** infers `time32` and lands as `TIME`, the one R route to that type; its truncation is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#' * **A plain list of vectors** lands as `LIST` through arrow.
#' * **A matrix column** lands as `ARRAY` through nanoarrow.
#'
#' # Geometry
#'
#' `GEOMETRY` and the `spatial` extension's own types through Arrow, and where the geoarrow and sf packages meet them:
#'
#' * **`GEOMETRY` exports as `geoarrow.wkb`, with the column's CRS in the field's metadata,**
#'   as PROJJSON where the core or `spatial` knows the CRS, and as its identifier otherwise:
#'   `OGC:CRS84` exports as PROJJSON without `spatial`, and `EPSG:4267` only with it.
#'   It stays `geoarrow.wkb` under every export setting, `arrow_lossless_conversion` included;
#'   `arrow_large_buffer_size` makes its storage `large_binary`, and an `arrow_output_version` from `'1.4'` makes it `binary_view`.
#'   With the geoarrow package loaded, both readers convert it to a `geoarrow_vctr` in each of those layouts, arrow the view one included.
#' * **sf reads a result through GeoArrow in one call.**
#'   With geoarrow loaded, `sf::st_as_sf(dbGetQueryArrow(con, sql))` gives an `sf` whose geometries and CRS equal the source's,
#'   in the large and view layouts too,
#'   and so do [sf::st_as_sf()] of the result's `arrow::as_arrow_table()`,
#'   and [sf::st_as_sfc()] of the `geoarrow_vctr` column of `as.data.frame()`.
#' * **The `spatial` extension's own types cross as their storage.**
#'   `POINT_2D` and the other point and box types export as a `struct`, `LINESTRING_2D` and `LINESTRING_3D` as a `list` of point structs,
#'   `POLYGON_2D` and `POLYGON_3D` as a list of those lists, and `WKB_BLOB` as `binary`, with `arrow_lossless_conversion` too,
#'   and each reader converts them as it converts those Arrow types.
#' * **GeoArrow WKB writes `GEOMETRY` with its CRS.**
#'   Encode the geometry column as WKB,
#'   as `geoarrow::as_geoarrow_vctr(sf::st_geometry(x), schema = geoarrow::geoarrow_wkb(crs = sf::st_crs(x)))`,
#'   or as a [wk::as_wkb()] column, which nanoarrow infers as `geoarrow.wkb` once geoarrow is loaded.
#'   Register `arrow::as_arrow_table(nanoarrow::as_nanoarrow_array_stream(df))` with `duckdb_register_arrow()`,
#'   and a `CREATE TABLE ... AS SELECT` from the view writes a `GEOMETRY` column with the CRS,
#'   which reads back into an `sf` equal to the one written.
#'   With `spatial` loaded, the type names the CRS by its identifier, as `GEOMETRY('EPSG:4267')`;
#'   without it, it keeps the PROJJSON it came as, and [sf::st_crs()] reads the same CRS from both.
#'   WKB without a CRS lands as plain `GEOMETRY`, and so do the large and view layouts DuckDB's own export makes.
#' * **A `geoarrow_vctr` column holds indices.**
#'   The `geoarrow_vctr` that `as.data.frame()` gives for a geometry in an Arrow result
#'   is an integer vector of indices into the Arrow data it holds, and writing it back is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md#limitations).
#'
#' # Limitations and reference
#'
#' The limitations are listed in the handbook, in [`usage/arrow-types/`](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/arrow-types/README.md).
#'
#' The routes through R vectors are documented in [duckdb_types],
#' and how a stream behaves, when it drains and what invalidates it, is documented in the handbook's [`integrations/`](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/integrations/README.md).
#' Every entry on this page was measured on DuckDB 1.5.5, nanoarrow 0.9.0 and arrow 25.0.1
#' in [`experiments/2026-09-27-arrow-types/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-09-27-arrow-types/README.md)
#' and [`experiments/2026-09-28-type-rereview/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-09-28-type-rereview/README.md),
#' and geometry also with geoarrow 0.4.4 and sf 1.1-3 in [`experiments/2026-09-27-geoarrow/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-09-27-geoarrow/README.md).
#'
#' @name duckdb_types_arrow
NULL