SQL ↔ Python types

SQL type	Python type	Notes
SMALLINT / INT / SERIAL	int	Arbitrary precision on the Python side.
BIGINT / BIGSERIAL	int	8-byte two’s complement on the wire.
INT8 / SERIAL8	int	The legacy 64-bit integer, a different wire format from BIGINT rather than an alias. See below.
FLOAT / SMALLFLOAT	float	IEEE 754 double / single.
DECIMAL(p,s) / MONEY	decimal.Decimal	Exact precision preserved.
CHAR / NCHAR	str	Fixed width, space-padded; trailing spaces stripped on decode.
VARCHAR / NVCHAR / LVARCHAR	str	Variable length. Decoded using client_locale.
BOOLEAN	bool
DATE	datetime.date
DATETIME YEAR TO …	datetime.datetime / datetime.time / datetime.date	The Python type depends on the field range.
INTERVAL DAY TO FRACTION	datetime.timedelta
INTERVAL YEAR TO MONTH	informix_db.IntervalYM	Custom type, because datetime.timedelta can’t represent year-month intervals.
BYTE / TEXT (legacy in-row blobs)	bytes / str
BLOB / CLOB (smart-LOBs)	informix_db.BlobLocator / informix_db.ClobLocator	Opaque server-side locators, 72 bytes. Read via cursor.read_blob_column, write via cursor.write_blob_column.
ROW(…)	informix_db.RowValue	Raw payload plus schema string, not decomposed into fields. See below.
SET(…) / MULTISET(…) / LIST(…)	informix_db.CollectionValue	Raw payload plus element schema, not iterable. See below.
NULL	None

INT8 and SERIAL8 are not BIGINT

Informix has two unrelated 64-bit integer types and they share nothing on the wire:

	Wire format
BIGINT / BIGSERIAL (type 52 / 53)	8 bytes, two’s complement, big-endian
INT8 / SERIAL8 (type 17 / 18)	10 bytes, sign-magnitude, halves stored high-last

Both decode to plain Python int, so this only matters if you’re reading the wire yourself. It’s worth knowing that INT8 stores +n and −n with identical magnitude bytes and the sign in a leading word. Decoding it as a signed 64-bit integer looks correct for every positive value and is wrong for every negative one.

INT8/SERIAL8 are common in schemas predating Informix 11.50, which is when BIGINT arrived.

Upgrade if you use these types

2026.08.27 fixed three decoding bugs: INT8/SERIAL8 weren’t decoded at all and returned raw bytes; NCHAR lost its first character; and BOOLEAN corrupted every column after it.

2026.08.31 fixed two more in LVARCHAR framing: a phantom pad byte on odd-length values, and a missing length field on NULLs. Either shifted every column selected after the LVARCHAR, producing wrong integers, strings missing their first character, or an outright IndexError on wide rows. The same release stopped DATETIME binds silently discarding sub-second precision.

2026.09.02 fixed two more, both found by a new check that verifies each row consumed exactly its own payload. Smart LOBs were read as a flat 72-byte field when they actually occupy 149 bytes populated and 5 when NULL, so a BLOB or CLOB anywhere but the last column shifted every column after it. A NULL SET / MULTISET / LIST / ROW skipped its length field and did the same. That release also made a mismatch raise where it happens, naming the column and the byte delta, instead of handing back plausible-looking wrong values.

2026.09.03 fixed one on the write path rather than the read path, which makes it the only entry here that affects data already stored. The :N placeholder rewriter was a regular expression and could not see a string literal, so it rewrote the inside of one: 'http://host:8080/x' was stored as 'http://host?/x'. Any HH:MM time, URL with a port, or key:value string was affected, whichever placeholder style you used. If you have written such values through this driver, they are worth checking.

If your schema has LVARCHAR columns, 2026.08.27 is not safe. If you select a BLOB or CLOB in any position other than last, or a nullable collection column, nothing before 2026.09.02 is safe. Upgrade.

LVARCHAR wire framing

Worth knowing if you’re reading captures. Every Informix server we’ve tested describes LVARCHAR columns as UDTVAR (type 40, extended_name='lvarchar'), and the value arrives wrapped in a UDT envelope:

[1-byte null indicator][4-byte length][content]

No padding, and the length field is present even when the indicator says NULL. NULL and empty string differ only in that indicator byte, since both carry a length of zero.

DATETIME field ranges

Informix’s DATETIME YEAR TO X is field-range typed. The Python type returned depends on which fields are present:

Range	Python type
YEAR TO YEAR through YEAR TO DAY	datetime.date
YEAR TO HOUR through YEAR TO FRACTION(5)	datetime.datetime
HOUR TO HOUR through HOUR TO FRACTION(5)	datetime.time

Decimal precision

DECIMAL columns preserve their declared precision and scale through the codec. A DECIMAL(10,2) column with value 123.45 decodes to Decimal("123.45") exactly, with no float intermediate.

For binding Decimal values into INSERT/UPDATE, the driver uses the column’s declared scale. Pass Decimal for exact values; float works but may cause rounding at the column scale.

NULL

NULL is None in both directions. Use IS NULL / IS NOT NULL in SQL, because WHERE x = ? with None returns no rows even where x IS NULL.

Type extensions

informix_db.IntervalYM represents INTERVAL YEAR TO MONTH. It takes a single total month count, mirroring the server’s own representation, with years and remainder_months available as derived properties:

from informix_db import IntervalYM

ym = IntervalYM(27)          # 2 years, 3 months
ym.years                     # 2
ym.remainder_months          # 3
str(ym)                      # '2-03'

cur.execute("INSERT INTO contracts (term) VALUES (?)", (ym,))
cur.execute("SELECT term FROM contracts WHERE id = ?", (1,))
result = cur.fetchone()[0]   # IntervalYM(months=27)

Negative intervals are supported; the sign lives on months and propagates to both derived properties.

ROW and collection columns

informix_db.RowValue and informix_db.CollectionValue are returned for ROW, SET, MULTISET, and LIST columns. Both are opaque wrappers, not decomposed values:

row_val.raw        # bytes: the server's textual representation, e.g. b"ROW('Alice',30)"
row_val.schema     # str:   the column's declared schema

coll_val.raw            # bytes, e.g. b'LIST{10,20,30}'
coll_val.kind           # 'set' | 'multiset' | 'list' | 'collection'
coll_val.element_schema # str

They are not iterable and do not expose fields by name. Fully parsing a composite type means reimplementing JDBC’s IfxComplexInput, which we haven’t done.

If you need individual fields today, project them in SQL. The server does the decomposition for you and you get ordinary typed columns back:

cur.execute("SELECT person.name, person.age FROM staff")   # str, int