Postgres type mapping¶

This page is generated from the type table in crates/rowx-postgres/src/types.rs, the single source of truth for the runtime codecs and for rowx check. Regenerate it with uv run python scripts/gen-types-doc.py.

• Result type is the Python type a column decodes to, and the leaf your row type must use.
• Accepted parameters are the Python types that can be interpolated into a parameter of that type. None (SQL NULL) is accepted for every parameter.
• Encoding is the wire format used to send a parameter.

Notes¶

• bool is never accepted for an integer parameter, and datetime is never accepted for a date parameter.
• A float4/float8 parameter takes an int, but an int above 2^53 loses precision.
• str is never treated as a sequence, so it cannot be bound to an array parameter.
• json and jsonb accept any JSON-serialisable value, always encoded with json.dumps. A str becomes a JSON string; to bind raw JSON text, write {s}::text::jsonb.
• Timezone rules are checked when encoding: a naive datetime for timestamptz (or an aware one for timestamp) raises EncodeError.
• A domain behaves as its base type.
• An array column decodes to list[X] (nested lists for multi-dimensional arrays). list[X] and list[X | None] both match, at every nesting level.
• Arrays and ranges are always sent in the binary format, whatever the format of their element or subtype is as a scalar (a numeric parameter is text, a numeric[] or numrange parameter is binary), so a value can never change the element or bound structure.
• void decodes to None and is only valid as a tuple element.
• A range decodes to rowx_postgres.Range[T], with T the leaf of its subtype, in the server's canonical form ([1,5] of int4range is [1, 6)). A None bound is an infinite side, and Range(empty=True) is the empty range. A user-defined range over int4, int8, numeric, date, timestamp or timestamptz decodes like the built-in range of that subtype and keeps whatever form its own canonical function gives it.
• interval decodes to datetime.timedelta, which raises DecodeError for a month component or ±infinity. Annotating the column rowx_postgres.Interval reads it losslessly as months, days and microseconds instead, and ±infinity keeps the server's sentinel fields (the maximum or minimum of each). A parameter takes either class.
• money decodes to rowx_postgres.Money(minor_units), the int8 the server sends. rowx holds no scale, because lc_monetary can change mid-session; Money.to_decimal(scale) takes it explicitly. To read the formatted text, cast the column to text.
• bit and varbit decode to a str of 0 and 1. A bit(n) parameter of any other length is rejected by the server.
• The geometric types decode to frozen dataclasses in rowx_postgres: Point(x, y), Line(a, b, c), LineSegment(start, end), Box(upper_right, lower_left), Path(points, closed), Polygon(points) and Circle(x, y, radius), with points a tuple of Point. The server canonicalises a box so its upper right corner comes first, and a decoded box shows that order. Equality compares the fields exactly, not with Postgres' epsilon-based ~=, so a NaN coordinate is never equal to anything. A path or polygon needs at least one point, and the server rejects a line whose a and b are both zero.
• hstore decodes to dict[str, str | None] (a NULL value is None; a key is never NULL, and a None key in a parameter raises EncodeError). ltree and lquery are str. cube is rowx_postgres.Cube(lower_left, upper_right), two tuple[float, ...] of one length; the corners keep the order the server stores (the first corner written is lower_left, not necessarily the minimum), a point has equal corners, and the codec sends equal corners as a point. A str that the server's parser rejects fails at Bind.
• Extension types (citext, hstore, ltree, lquery, cube) are recognised by name and owning extension, found through pg_depend, so a same-named type from another schema or no extension is unsupported-type in the checker and raises DecodeError at runtime (when the statement is prepared). The extension may live in any schema.
• Any type not listed here is unsupported-type in the checker and raises DecodeError at runtime (for example multiranges, composites and xml).

Differences from psycopg¶

Decoding the same server value with rowx and with psycopg gives equal results, except for these cases. A test asserts that this list matches the one it checks.

• timestamptz: rowx always returns a UTC-aware value, while psycopg uses the session TimeZone; the instants are equal.
• float4: rowx widens the exact 32-bit value to a float, while psycopg parses the shortest decimal text; the values are equal after rounding to 32 bits.
• char: A non-ASCII "char" is cut to one byte by the server, which is not valid UTF-8, so rowx raises DecodeError.
• interval: An interval with a month or year part raises DecodeError unless the annotation is rowx_postgres.Interval, while psycopg approximates a month as 30 days.
• date: An infinite date raises DecodeError, while psycopg raises its own DataError.
• timestamp: An infinite timestamp raises DecodeError, while psycopg raises its own DataError.
• void: void decodes to None, while psycopg returns an empty string.
• inet: rowx always returns an interface, even for a full-width mask, while psycopg returns an address for /32 and /128; the interface's ip is that address.
• int4range: rowx returns rowx_postgres.Range, while psycopg returns its own Range; the bounds are equal.
• int8range: rowx returns rowx_postgres.Range, while psycopg returns its own Range; the bounds are equal.
• numrange: rowx returns rowx_postgres.Range, while psycopg returns its own Range; the bounds are equal.
• daterange: rowx returns rowx_postgres.Range, while psycopg returns its own Range; the bounds are equal, but an infinite bound of this subtype raises DecodeError, while psycopg raises its own DataError.
• tsrange: rowx returns rowx_postgres.Range, while psycopg returns its own Range; the bounds are equal, but an infinite bound of this subtype raises DecodeError, while psycopg raises its own DataError.
• tstzrange: rowx returns rowx_postgres.Range, while psycopg returns its own Range; the bounds are equal as instants, but an infinite bound of this subtype raises DecodeError, while psycopg raises its own DataError.
• money: rowx returns rowx_postgres.Money, the int8 minor units with no scale, while psycopg returns the text the server formats under lc_monetary.
• point: rowx returns rowx_postgres.Point, while psycopg returns the text the server formats; the coordinates are equal.
• line: rowx returns rowx_postgres.Line, while psycopg returns the text the server formats; the coordinates are equal.
• lseg: rowx returns rowx_postgres.LineSegment, while psycopg returns the text the server formats; the coordinates are equal.
• box: rowx returns rowx_postgres.Box, while psycopg returns the text the server formats; the coordinates are equal.
• path: rowx returns rowx_postgres.Path, while psycopg returns the text the server formats; the coordinates are equal.
• polygon: rowx returns rowx_postgres.Polygon, while psycopg returns the text the server formats; the coordinates are equal.
• circle: rowx returns rowx_postgres.Circle, while psycopg returns the text the server formats; the coordinates are equal.
• cube: rowx returns rowx_postgres.Cube, while psycopg returns the text the server formats; the coordinates are equal.