R/duckdb_types.R

In duckdb: DBI Package for the DuckDB Database Management System

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

#' DuckDB data types in R
#'
#' @description
#' This page documents every DuckDB type as it crosses to R and back:
#' what a value of the type becomes when read, which R value writes it again, and what to do where neither works.
#' See [duckdb_types_arrow] for a description of the conversion via Arrow.
#'
#' @details
#' # The routes
#'
#' **Reading.**
#' [dbGetQuery()] converts each column by its type, and four [dbConnect()] arguments change the shape,
#' with defaults `bigint = "numeric"`, `array = "none"`, `map = "data.frame"` and `geometry = "blob"`.
#' [dbGetQueryArrow()] hands out the engine's own Arrow export instead,
#' and what each type becomes there, and in the R readers that convert the stream, is documented in [duckdb_types_arrow].
#' A cast to `VARCHAR` in the query reads any type as text.
#'
#' **Writing.**
#' [dbWriteTable()] and [duckdb_register()] take a column's type from its R class,
#' and `field.types` casts that column to the type it names, from any value that casts.
#' [dbAppendTable()] casts to the type of the existing column, and a parameter (`params =`) binds by its R class, cast by the query.
#' A `character` column holding a value's text form writes every scalar type through `field.types`, because DuckDB parses the text it prints.
#' Arrow, registered with [duckdb_register_arrow()], writes the types no R class does.
#'
#' # Numbers
#'
#' The [numeric](https://duckdb.org/docs/current/sql/data_types/numeric) and [boolean](https://duckdb.org/docs/current/sql/data_types/boolean) types:
#'
#' * **`BOOLEAN`** (`BOOL`, `LOGICAL`) reads as `logical`, and `logical` writes it.
#' * **`TINYINT`, `SMALLINT`, `UTINYINT`, `USMALLINT`** read as `integer`, exactly.
#'   `integer` writes `INTEGER`, and `field.types` names the narrower type.
#' * **`INTEGER`** (`INT4`, `INT`, `SIGNED`) reads as `integer`, exactly but for the minimum, and `integer` writes it.
#' * **`UINTEGER`** reads as `numeric`, exactly; `numeric` writes `DOUBLE`, and `field.types` names `UINTEGER`.
#' * **`BIGINT`** (`INT8`, `LONG`) reads as `numeric`, exact up to 2^53, and its rounding past that is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#'   With `bigint = "integer64"` it reads as `bit64::integer64`, exact but for the minimum.
#'   An `integer64` column or parameter writes `BIGINT` whatever `bigint` says.
#' * **`UBIGINT`** reads as `numeric`, exact up to 2^53, and its rounding past that is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#'   With `bigint = "integer64"` it reads as `integer64`, which holds the values below 2^63.
#'   Below 2^63, the `integer64` it reads as writes it back through `field.types`, and its text writes any value.
#' * **`HUGEINT`, `UHUGEINT`** read as `numeric`, and `bigint` does not change that; their rounding is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#'   Their text is exact both ways.
#' * **`BIGNUM`** (`VARINT`) reads and writes through its text, and Arrow writes it (see [duckdb_types_arrow]).
#' * **`DECIMAL(width, scale)`** (`NUMERIC`) reads as `numeric` at every width; its rounding is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#'   Its text is exact both ways, and Arrow writes it exactly.
#' * **`FLOAT`** (`REAL`) and **`DOUBLE`** read as `numeric`, and `numeric` writes `DOUBLE`.
#'   `NaN` reads and writes as `NaN`, never as `NA`, and `NA` is `NULL` in both directions.
#'
#' # Text and binary
#'
#' The [text](https://duckdb.org/docs/current/sql/data_types/text), [blob](https://duckdb.org/docs/current/sql/data_types/blob)
#' and [bitstring](https://duckdb.org/docs/current/sql/data_types/bitstring) types, and `UUID`:
#'
#' * **`VARCHAR`** (`CHAR`, `BPCHAR`, `TEXT`, `STRING`) reads as `character`, and `character` writes it;
#'   non-UTF-8 text is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#' * **`BLOB`** (`BYTEA`, `BINARY`, `VARBINARY`) reads as a list of raw vectors.
#'   A `blob::blob` or a list of raw vectors writes it.
#' * **`BIT`** (`BITSTRING`) reads and writes through its text.
#' * **`UUID`** reads as `character`, lowercase and hyphenated.
#'   `character` writes `VARCHAR`, and `field.types` makes it a `UUID`.
#'
#' # Dates and times
#'
#' The [date](https://duckdb.org/docs/current/sql/data_types/date), [time](https://duckdb.org/docs/current/sql/data_types/time),
#' [timestamp](https://duckdb.org/docs/current/sql/data_types/timestamp) and [interval](https://duckdb.org/docs/current/sql/data_types/interval) types:
#'
#' * **`DATE`** reads as `Date`, and a `Date` writes it, stored as double or as integer.
#' * **`TIME`** reads as `difftime` in seconds.
#'   Its text writes it through `field.types`, and so does Arrow (see [duckdb_types_arrow]).
#' * **`TIME_NS`** reads through Arrow, and to the microsecond through a cast to `TIME` in the query.
#'   Its text writes it, and so does Arrow.
#' * **`TIMETZ`** (`TIME WITH TIME ZONE`) reads as the `difftime` of its local time.
#'   The offset it drops is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#'   Its text writes it.
#' * **`TIMESTAMP_S`, `TIMESTAMP_MS`, `TIMESTAMP`** (`DATETIME`) read as `POSIXct`.
#'   `POSIXct` writes `TIMESTAMP`, the instant in UTC to the nearest microsecond, a tie to even, and `field.types` names the other precisions.
#' * **`TIMESTAMP_NS`** reads as `POSIXct`.
#'   `POSIXct` writes it to the microsecond through `field.types`, and Arrow writes it directly.
#' * **`TIMESTAMPTZ`** (`TIMESTAMP WITH TIME ZONE`) reads as `POSIXct`.
#'   `POSIXct` writes the plain `TIMESTAMP` of the same instant; `field.types` makes it `TIMESTAMPTZ`,
#'   and Arrow writes it directly.
#' * **`INTERVAL`** reads as `difftime` in seconds, counting a month as 30 days and a day as 24 hours.
#'   A `difftime` in any unit, or an `hms`, writes `INTERVAL`, to the nearest microsecond, a tie to even.
#'
#' # Enums and nested types
#'
#' The [enum](https://duckdb.org/docs/current/sql/data_types/enum) type,
#' and the [nested](https://duckdb.org/docs/current/sql/data_types/overview) ones:
#'
#' * **`ENUM`** reads as `factor`, with every value of the type as a level.
#'   A `factor` or `ordered` column writes `ENUM` of its levels; a `factor` parameter binds as `VARCHAR`.
#' * **`ARRAY`** (`INTEGER[3]`) reads with `array = "matrix"`, as a matrix with a row per value.
#'   A matrix column writes it.
#' * **`LIST`** (`INTEGER[]`) reads as a list of vectors, `NULL` for a `NULL` row, and a list column whose elements share a type writes it.
#' * **`MAP`** reads as a list of `data.frame(key, value)`, which writes a list of structs unless `field.types` names the map.
#'   With `map = "list_of"`, the [vctrs::list_of()] it reads as writes back as `MAP` without `field.types`
#'   ([#200](https://github.com/duckdb/duckdb-r/issues/200)),
#'   and a list column of named lists writes a list of structs, an entry per name,
#'   valued by the first element of the name's value, or by `NULL` where that value is `NULL` or empty.
#'   Its text casts to `MAP` in the query and as a parameter.
#' * **`STRUCT`** (`ROW`) reads as a data frame column.
#'   A data frame column writes it, and a data frame parameter binds a struct per row.
#' * **`UNION`** reads in the query through `union_tag()`, `union_extract()` or a cast to `VARCHAR`, and an Arrow result carries it.
#'   A column of a member's type writes it through `field.types`, which picks that member; text picks the `VARCHAR` member.
#' * **`VARIANT`** reads as a list, each value converted by its own type.
#'   A column of the value's type writes it through `field.types`.
#'
#' # Geometry
#'
#' The [`GEOMETRY`](https://duckdb.org/docs/current/sql/data_types/geometry) type, its coordinate reference system (CRS),
#' and the `spatial` extension's own types:
#'
#' * **`GEOMETRY`** reads as WKB.
#'   With `geometry = "blob"`, the default, it reads as a list of raw vectors;
#'   with `geometry = "wk"`, as `wk_wkb`, carrying the column's CRS as an attribute, which [sf::st_as_sfc()] converts onward, CRS included.
#'   The type is core since DuckDB 1.5, so reading one needs no extension;
#'   the geometry functions are the [`spatial` extension's](https://duckdb.org/docs/current/core_extensions/spatial/overview).
#'   Arrow carries the column as GeoArrow WKB with its CRS, in both directions (see [duckdb_types_arrow]).
#' * **WKT writes `GEOMETRY`.**
#'   A `character` column of WKT, as [sf::st_as_text()] makes it, writes a `GEOMETRY` column with `field.types = c(geom = "GEOMETRY")`
#'   and appends to one with `dbAppendTable()`, because the cast from `VARCHAR` parses WKT.
#'   Naming the CRS in the type, as `"GEOMETRY('EPSG:4267')"`, gives the column its CRS.
#'   Writing WKB as raw vectors, an `sf` object or an `sfc` column is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#' * **The `spatial` extension's own types are aliases, and read as what they alias.**
#'   `POINT_2D`, `POINT_3D`, `POINT_4D`, `BOX_2D` and `BOX_2DF` are structs, and read as data frame columns;
#'   `LINESTRING_2D` and `LINESTRING_3D` are lists of point structs, and read as lists of data frames;
#'   `POLYGON_2D` and `POLYGON_3D` are lists of those rings, and read as lists of lists;
#'   `WKB_BLOB` is a `BLOB`, and reads as raw vectors.
#'   The same shapes write the plain struct or list, and `field.types` naming the alias casts back to it.
#'   They cast to `GEOMETRY` in the query,
#'   and the point, linestring, polygon and WKB types cast from it, as `'POINT (1 2)'::GEOMETRY::POINT_2D`.
#'
#' # Everything else
#'
#' * **An untyped `NULL` comes back as `NA_integer_`,**
#'   matching the engine's own `SELECT NULL`;
#'   mapping it to logical `NA` instead was declined ([#155](https://github.com/duckdb/duckdb-r/issues/155)).
#'   A typed `NULL`, as a scanned logical column or a bound `NA` parameter, round-trips as logical `NA`.
#' * **`JSON`**, the [`json` extension's](https://duckdb.org/docs/current/data/json/json_type) alias of `VARCHAR`, reads as `character`.
#'   Its text writes it through `field.types`.
#' * **`INET`**, the [`inet` extension's](https://duckdb.org/docs/current/core_extensions/inet) address type,
#'   reads as a data frame column whose `address` is a `HUGEINT` read as a double, exact for IPv4;
#'   an IPv6 address is a [limitation](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md#limitations).
#'   Its text reads and writes it exactly.
#'
#' # Limitations and reference
#'
#' The limitations are listed in the handbook, in [`usage/types/`](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/types/README.md).
#'
#' The mapping is implemented in [`src/types.cpp`](https://github.com/duckdb/duckdb-r/blob/main/src/types.cpp) (R vector to `LogicalType`) and [`src/transform.cpp`](https://github.com/duckdb/duckdb-r/blob/main/src/transform.cpp) (the way back).
#' The list of types is DuckDB's own [documentation](https://duckdb.org/docs/current/sql/data_types/overview) for the release vendored here,
#' and every entry on this page was measured on DuckDB 1.5.5, in [`experiments/2026-09-26-type-catalog/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-09-26-type-catalog/README.md),
#' [`experiments/2026-09-27-review-limits/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-09-27-review-limits/README.md),
#' [`experiments/2026-09-28-type-rereview/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-09-28-type-rereview/README.md)
#' or, for geometry route by route, [`experiments/2026-08-09-spatial-interop/`](https://github.com/duckdb/duckdb-r/blob/main/experiments/2026-08-09-spatial-interop/README.md).
#' Which zone labels a timestamp is documented in the handbook's [`timestamps/`](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/timestamps/README.md).
#'
#' What `expr_constant(NA)` builds in the relational API is documented in the handbook's [`relational/`](https://github.com/duckdb/duckdb-r/blob/main/handbook/usage/relational/README.md).
#'
#' @name duckdb_types
NULL