Skip to main content

DuckDB type support

Foundations JDBC supports DuckDB's type system, including nested types (LIST, STRUCT, MAP, UNION) and extended integer types.

Query Analysis on DuckDB is column-type-only

DuckDB's JDBC driver doesn't report column nullability or parameter metadata, so QueryChecker's .opt() and wrong-parameter-type checks silently pass on DuckDB. Column type mismatches are still caught. If you test against DuckDB in-memory and deploy against PostgreSQL or another dialect, run analysis against the production dialect too — it will find issues DuckDB can't. See Query Analysis: Database Behavior for the full matrix.

Integer types (signed)​

DuckDB TypeJava TypeRange
TINYINTByte-128 to 127
SMALLINTShort-32,768 to 32,767
INTEGER / INTInteger-2^31 to 2^31-1
BIGINTLong-2^63 to 2^63-1
HUGEINTBigInteger-2^127 to 2^127-1
val tinyType: DuckDbType<Byte> = DuckDbTypes.tinyint
val intType: DuckDbType<Int> = DuckDbTypes.integer
val hugeType: DuckDbType<BigInteger> = DuckDbTypes.hugeint

Integer types (unsigned)​

DuckDB TypeJava TypeRange
UTINYINTUint10 to 255
USMALLINTUint20 to 65,535
UINTEGERUint40 to 2^32-1
UBIGINTUint80 to 2^64-1
UHUGEINTBigInteger0 to 2^128-1
val utinyType: DuckDbType<Uint1> = DuckDbTypes.utinyint
val uintType: DuckDbType<Uint4> = DuckDbTypes.uinteger
val uhugeType: DuckDbType<BigInteger> = DuckDbTypes.uhugeint

Floating-point types​

DuckDB TypeJava TypeNotes
FLOAT / FLOAT4 / REALFloat32-bit IEEE 754
DOUBLE / FLOAT8Double64-bit IEEE 754
val floatType: DuckDbType<Float> = DuckDbTypes.float_
val doubleType: DuckDbType<Double> = DuckDbTypes.double_

Fixed-point types​

DuckDB TypeJava TypeNotes
DECIMAL(p,s)BigDecimalArbitrary precision
NUMERIC(p,s)BigDecimalAlias for DECIMAL
val decimalType: DuckDbType<BigDecimal> = DuckDbTypes.decimal
val precise: DuckDbType<BigDecimal> = DuckDbTypes.decimalOf(18, 6)  // DECIMAL(18,6)

Boolean type​

DuckDB TypeJava Type
BOOLEAN / BOOLBoolean
val boolType: DuckDbType<Boolean> = DuckDbTypes.boolean_

String types​

DuckDB TypeJava TypeNotes
VARCHAR / STRING / TEXTStringVariable length
CHAR(n)StringFixed length
val varcharType: DuckDbType<String> = DuckDbTypes.varchar
val charType: DuckDbType<String> = DuckDbTypes.char_Of(10)

Binary types​

DuckDB TypeJava Type
BLOB / BYTEA / BINARY / VARBINARYbyte[]
val blobType: DuckDbType<ByteArray> = DuckDbTypes.blob

Bit string type​

DuckDB TypeJava TypeNotes
BIT / BITSTRINGStringString of 0s and 1s
val bitType: DuckDbType<String> = DuckDbTypes.bit
val bit8: DuckDbType<String> = DuckDbTypes.bitOf(8)  // BIT(8)

Date/time types​

DuckDB TypeJava TypeNotes
DATELocalDateNaive date, no zone
TIMELocalTimeNaive time, no zone
TIMESTAMP / DATETIMELocalDateTimeNaive timestamp, no zone
TIMESTAMP WITH TIME ZONEInstantUTC instant — see note below
TIME WITH TIME ZONEOffsetTimeTime of day with offset preserved
INTERVALDurationTime duration
val dateType: DuckDbType<LocalDate> = DuckDbTypes.date
val tsType: DuckDbType<LocalDateTime> = DuckDbTypes.timestamp
val tstzType: DuckDbType<Instant> = DuckDbTypes.timestamptz
val intervalType: DuckDbType<Duration> = DuckDbTypes.interval
TIMESTAMP WITH TIME ZONE is not what the name suggests

Despite the SQL keyword, DuckDB does not store any zone or offset with a TIMESTAMPTZ value — it stores an INT64 count of microseconds since the Unix epoch (see the DuckDB timestamp docs). The original offset or region is used only for parsing on input, then discarded. Reads render the instant in the session timezone, which is a display convenience, not persisted state.

Because the storage is a universal instant, the library maps this column to java.time.Instant — the Java type with the same semantics. Using OffsetDateTime would surface the JDBC driver's cosmetic "render in session offset" output as if it were data. Instant is identical in spirit to PostgreSQL's timestamptz mapping (see postgresql.md).

Note: TIME WITH TIME ZONE (TIMETZ) is a distinct case: DuckDB's JDBC driver does preserve the original offset on round-trip (verified empirically), so that column maps to OffsetTime, not Instant. The DuckDB CLI renders TIMETZ in session-TZ which can make it look normalized, but that's display-only.

Timestamp precision variants​

DuckDB TypeJava TypePrecision
TIMESTAMP_SLocalDateTimeSeconds
TIMESTAMP_MSLocalDateTimeMilliseconds
TIMESTAMPLocalDateTimeMicroseconds (default)
TIMESTAMP_NSLocalDateTimeNanoseconds
val tsSeconds: DuckDbType<LocalDateTime> = DuckDbTypes.timestamp_s
val tsMillis: DuckDbType<LocalDateTime> = DuckDbTypes.timestamp_ms
val tsNanos: DuckDbType<LocalDateTime> = DuckDbTypes.timestamp_ns

UUID type​

DuckDB TypeJava Type
UUIDjava.util.UUID
val uuidType: DuckDbType<UUID> = DuckDbTypes.uuid

JSON type​

DuckDB TypeJava Type
JSONJson
val jsonType: DuckDbType<Json> = DuckDbTypes.json

val data: Json = Json("{\"name\": \"DuckDB\"}")

Enum type​

// Define your Kotlin enum
enum class Status { PENDING, ACTIVE, COMPLETED }

// Create DuckDbType — reified, no arguments beyond the SQL type name
val statusType: DuckDbType<Status> = DuckDbTypes.ofEnum<Status>("status")
sqlType must match the declared type name

The first argument to ofEnum(sqlType, ...) is used to cast bound parameters (e.g. CAST(? AS status)) and must match the name used in CREATE TYPE status AS ENUM(...) and in the column's declared type. If the column is typed status_t but you call ofEnum("status", ...), inserts fail with Type with name status does not exist.

LIST types​

Any type can be made into a variable-length list with .list(). DuckDB renders as T[] and the Java representation is List<T>:

DuckDB TypeJava TypeCreated via
INTEGER[]List<Integer>integer.list()
VARCHAR[]List<String>varchar.list()
DATE[]List<LocalDate>date.list()
......anyType.list()
// Any type can be made into a list with .list()
val listInt: DuckDbType<List<Int>> = DuckDbTypes.integer.list()
val listStr: DuckDbType<List<String>> = DuckDbTypes.varchar.list()
val listDouble: DuckDbType<List<Double>> = DuckDbTypes.double_.list()
val listUuid: DuckDbType<List<UUID>> = DuckDbTypes.uuid.list()
val listDate: DuckDbType<List<LocalDate>> = DuckDbTypes.date.list()
val listDecimal: DuckDbType<List<BigDecimal>> = DuckDbTypes.decimal.list()

ARRAY types​

Fixed-size arrays use .array(size). DuckDB enforces that every row has exactly size elements — ideal for embeddings, RGB colors, or any dense fixed-shape tensor. The Java representation is still List<T>:

DuckDB TypeJava TypeCreated via
FLOAT[1536]List<Float>float_.array(1536)
INTEGER[3]List<Integer>integer.array(3)
// Fixed-size ARRAY — every row must have exactly `size` elements.
val embedding: DuckDbType<List<Float>> = DuckDbTypes.float_.array(1536)
val rgb: DuckDbType<List<Int>> = DuckDbTypes.integer.array(3)

Nested collections​

LIST and ARRAY compose freely in any combination:

SQL TypeJava TypeCreated via
T[][]List<List<T>>t.list().list()
T[m][n]List<List<T>>t.array(n).array(m)
T[][n]List<List<T>>t.list().array(n)
T[m][]List<List<T>>t.array(m).list()
// Nested collections compose — any combination is legal.
val grid: DuckDbType<List<List<Int>>> = DuckDbTypes.integer.list().list()
val matrix: DuckDbType<List<List<Float>>> = DuckDbTypes.float_.array(3).array(3)
val variableRows: DuckDbType<List<List<String>>> = DuckDbTypes.varchar.list().array(4)

MAP types​

DuckDB's MAP type for key-value pairs:

DuckDB TypeJava Type
MAP(VARCHAR, INTEGER)Map<String, Integer>
MAP(VARCHAR, VARCHAR)Map<String, String>
// Create map types using the mapTo() combinator
val mapStrInt: DuckDbType<Map<String, Int>> = DuckDbTypes.varchar.mapTo(DuckDbTypes.integer)
val mapStrStr: DuckDbType<Map<String, String>> = DuckDbTypes.varchar.mapTo(DuckDbTypes.varchar)
val mapUuidTime: DuckDbType<Map<UUID, LocalTime>> = DuckDbTypes.uuid.mapTo(DuckDbTypes.time)

// Works with any combination of types
val mapStrDate: DuckDbType<Map<String, LocalDate>> = DuckDbTypes.varchar.mapTo(DuckDbTypes.date)

Struct types​

DuckDB STRUCT types are built from a RowCodecNamed via compositeOf:

DuckDbType<Person> personType = DuckDbTypes.compositeOf("person",
    RowCodec.<Person>namedBuilder()
        .field("name", DuckDbTypes.varchar, Person::name)
        .field("age", DuckDbTypes.integer, Person::age)
        .build(Person::new));

// List of structs (variable-length)
DuckDbType<List<Person>> personListType = personType.list();

// Fixed-size array of structs (e.g. always 3 members)
DuckDbType<List<Person>> trioType = personType.array(3);

UNION types​

DuckDB's UNION type for tagged unions:

// Unions are typically handled via generated code
// The variants are defined by the table schema

Nullable types​

Any type can be made nullable using .opt():

val notNull: DuckDbType<Int> = DuckDbTypes.integer
val nullable: DuckDbType<Int?> = DuckDbTypes.integer.opt()

Custom domain types​

Wrap base types with custom Java types using transform:

// Wrapper type
data class ProductId(val value: Long)

// Create DuckDbType from bigint
val productIdType: DuckDbType<ProductId> = DuckDbTypes.bigint.transform(::ProductId, ProductId::value)