Skip to contents

DuckDB data types in R

Source: R/duckdb_types.R
duckdb_types.Rd

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.

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 and 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. 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. 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. 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. 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, blob and bitstring types, and UUID:

  • VARCHAR (CHAR, BPCHAR, TEXT, STRING) reads as character, and character writes it; non-UTF-8 text is a limitation.

  • 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, time, timestamp and 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. Its text writes it.

  • TIMESTAMP_S, TIMESTAMP_MS, TIMESTAMP (DATETIME) read as POSIXct. POSIXct writes TIMESTAMP, the instant in UTC, 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.

Enums and nested types

The enum type, and the nested 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), 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 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. 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.

  • 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). A typed NULL, as a scanned logical column or a bound NA parameter, round-trips as logical NA.

  • JSON, the json extension's alias of VARCHAR, reads as character. Its text writes it through field.types.

  • INET, the inet extension's 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. Its text reads and writes it exactly.

Limitations and reference

The limitations are listed in the handbook, in usage/types/.

The mapping is implemented in src/types.cpp (R vector to LogicalType) and src/transform.cpp (the way back). The list of types is DuckDB's own documentation 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/, experiments/2026-09-27-review-limits/, experiments/2026-09-28-type-rereview/ or, for geometry route by route, experiments/2026-08-09-spatial-interop/. Which zone labels a timestamp is documented in the handbook's timestamps/.

What expr_constant(NA) builds in the relational API is documented in the handbook's relational/.