Skip to main content

ArpePGSQL — Data types

How PostgreSQL types map to Apache Arrow on the fetch (read) path, and how Arrow types map back to PostgreSQL on the ingest/bind (write) path.

Both directions use the COPY-binary fast path — read via COPY TO / the binary result format, write via COPY FROM STDIN (binary) — so there is no text round-trip for the common types. The headline case is NUMERIC, decoded straight from PostgreSQL's base-10000 binary layout into a decimal128/decimal256 with no string detour.

SQL type name in the field metadata​

Version

Since ArpePGSQL v0.4.3.

Every field the driver returns (query results, ExecuteSchema and GetTableSchema) carries the column's SQL type in the field metadata key ARPEPGSQL:type, spelled the way PostgreSQL's format_type spells it (what psql \d shows): for example integer, character varying(1000), numeric(10,2), timestamp(3) with time zone or integer[]. Enum and extension types (such as PostGIS) are named by the server; a domain reports its base type. On geospatial columns the key sits alongside the geoarrow.wkb extension metadata.

Read path: PostgreSQL → Arrow​

Numeric​

PostgreSQL typeArrow typeNotes
boolbool
int2int16
int4int32
int8int64
float4float32
float8float64
oidint32
numeric(p,s)decimal128(p,s) (p ≤ 38) · decimal256 (39–76) · utf8 (> 76 / unconstrained, or numeric_format=string)native base-10000 binary decode, no text round-trip; NaN handling: see NUMERIC special values

An unconstrained numeric (no declared precision) has no fixed Arrow decimal width, so it renders as utf8 (canonical PostgreSQL text).

NUMERIC special values​

Version

Since ArpePGSQL v0.4.3.

PostgreSQL NUMERIC can hold NaN, and (unconstrained only, PostgreSQL 14+) Infinity / -Infinity. Arrow decimals cannot represent them, so by default a NaN read into a decimal128/decimal256 column fails the read with an error naming the column, rather than silently changing the value:

column "price": NUMERIC NaN/Infinity cannot be represented as decimal128(15,2);
set adbc.arpepgsql.numeric_format=string to read NUMERIC as text (lossless), or
adbc.arpepgsql.numeric_special=null to read NaN as NULL

Two options control this:

OptionEffect
adbc.arpepgsql.numeric_format=stringEvery NUMERIC column is returned as utf8 in PostgreSQL's text form ("12345.67", "NaN", "Infinity"). Lossless.
adbc.arpepgsql.numeric_special=nullKeep typed decimals and read NaN as NULL (indistinguishable from a real SQL NULL).

Unconstrained numeric is always utf8, so its special values come back as text and never trigger the error.

Character & binary​

PostgreSQL typeArrow typeNotes
text / varchar / bpchar (char(n)) / nameutf8
uuidutf836-char canonical form; lowercase by default, casing via uuid_casing
json / jsonbutf8canonical PostgreSQL text
inet / cidr / macaddrutf8canonical PostgreSQL text
bit / varbitutf8canonical PostgreSQL text
xmlutf8text export
byteabinaryraw bytes
unknown / exotic OIDs (arrays, ranges, enums, …)binaryraw wire bytes

Temporal​

PostgreSQL typeArrow typeNotes
datedate32days since epoch
time(p)time32[s|ms] or time64[us]unit follows the column precision
timetzutf8text export (Arrow has no time-with-offset type)
timestamp(p)timestamp[s|ms|us]unit follows the column precision
timestamptz(p)timestamp[s|ms|us, tz=UTC]stored UTC instant
intervalmonth_day_nanoArrow's month/day/nanosecond interval

PostgreSQL's microsecond storage resolution means true Arrow-nanosecond precision is not reachable.

Geospatial (PostGIS)​

PostgreSQL typeArrow typeNotes
geometry / geographybinary + geoarrow.wkb extension (default)EWKB passes straight through; see Geospatial types

Geospatial types​

Version

Since ArpePGSQL v0.2.4.

PostGIS geometry / geography columns are read over the COPY-binary path as EWKB — which is already a valid GeoArrow geoarrow.wkb encoding, so the bytes pass straight through as Arrow binary with no server-side ST_AsBinary round-trip. The adbc.arpepgsql.geospatial database option controls the Arrow field metadata:

ValueOutputNotes
geoarrow.wkb (default)Arrow binary + geoarrow.wkb extensionEWKB plus ARROW:extension:name=geoarrow.wkb and an ARROW:extension:metadata JSON object; read directly by GeoPandas / GeoParquet / DuckDB
wkbArrow binaryEWKB bytes, no extension metadata
binaryArrow binaryopaque binary, no extension metadata

How it works. The geometry/geography type OIDs are resolved per session from pg_type; when PostGIS is not installed this is a clean no-op. In geoarrow.wkb mode the extension metadata carries edges: spherical for geography (planar geometry omits it) and crs: EPSG:<srid> taken from the column's PostGIS typmod. CRS is emitted only for a typmod-constrained column with an SRID other than 0/4326 (mirroring the GeoParquet convention); a plain geometry column with mixed/unconstrained SRID omits it. The SRID embedded in each EWKB value is preserved regardless of the metadata.

Write path: Arrow → PostgreSQL (ingest & bind)​

On adbc_ingest (COPY FROM STDIN, binary) and prepared-statement bind (extended protocol), Arrow arrays are routed to PostgreSQL types.

Bulk ingest introspects the target table's column OIDs (pg_catalog.pg_attribute) before writing, so Arrow columns land in the right receive format even when the source Arrow type is a generic utf8:

  • Integers, floats, bool, decimal, date, time, timestamp (with and without timezone), and binary map directly.
  • Arrow utf8 lands correctly in json, jsonb, inet, cidr, macaddr, bit/varbit, and uuid columns (the encoder transforms the text into each type's binary receive format).
  • Arrow month_day_nano lands in interval columns.

Both the array-bind and stream-bind ingest paths introspect the target (stream bind consumes its input one batch at a time). The temp-table same-session fast path is the one exception: it keeps Arrow-only type inference (no OID transforms), so a utf8 bound into a jsonb/inet/… column through it is rejected by the server.

Arrow inputs with no PostgreSQL equivalent, and Arrow nanosecond temporal precision (PostgreSQL tops out at microseconds), are rejected with NOT_IMPLEMENTED.

See also​