The PostgreSQL data types your schema expects, native on Informix.
A family of PostgreSQL-compatible data types, implemented as native Informix user-defined types: the zone-aware instant timestamptz, the range and multirange types, uuid, citext, and the network types inet, cidr and macaddr. A schema written for PostgreSQL creates and queries on Informix with the same DDL and the same canonical text. Every type is held to one standard: it must behave like its PostgreSQL counterpart, verified by a suite that runs identically on Informix 14, Informix 15 and PostgreSQL.
The same DDL as PostgreSQL
A timestamptz event log, a tstzrange booking calendar, an inet device inventory, a citext e-mail column, a numrange price band — each declared with the same type name it carries on PostgreSQL, and stored with the same canonical text.
Inside the engine, not beside it
Each type is an opaque user-defined type whose behaviour is defined in C and registered in the engine. It stores, sorts, indexes, compares and casts like a first-class Informix type, addressed in ordinary SQL.
Proven equivalent, not asserted
A single test exercises each behaviour on three engines — Informix 14, Informix 15 and PostgreSQL — against an engine-independent reference. The cross-engine suite runs 129 assertions green.
Fills the gaps, does not duplicate
Types Informix already ships — MONEY, DECIMAL, native JSON — are deliberately left alone. The extension adds only the types that have no native Informix equivalent.
How parity is proven
The type-system gap, closed inside the engine
Informix has a rich built-in type system, but a handful of types that application developers reach for on PostgreSQL have no native Informix equivalent. A zone-aware instant, range and multirange types, network address types and case-insensitive text are the everyday building blocks of bookings, validity windows, price bands, device inventories and user identifiers. Code ported from PostgreSQL — or a single schema meant to run on both engines — needs those types to behave the same way on Informix: same literals, same sort order, same containment and overlap semantics.
The Informix DataBlade mechanism lets the extension add such types inside the engine as opaque user-defined types. The storage and behaviour are defined in C; the type then stores, sorts, indexes, compares and casts like any built-in type. Each type is held to one hard standard — it must behave like its PostgreSQL counterpart — and that standard is enforced by a test that runs on both engines and asserts they agree.
The types, used from ordinary SQL
Four families, each declared with the PostgreSQL type name and queried with the PostgreSQL canonical text.
timestamptz — a zone-aware instant
A PostgreSQL-faithful timestamp with time zone: an absolute instant, stored normalised to UTC, where the input zone normalises and the output renders but the zone itself is never stored. Two events entered in different offsets sort by the same instant. timestamptz_now() returns the current instant; tstz_epoch() matches PostgreSQL's extract(epoch …); tstz_at_utc() and tstz_at_zone() render at a chosen offset.
Range and multirange types
Intervals between two bounds, each inclusive or exclusive, either possibly infinite, plus the distinguished empty range. tstzrange, daterange, int8range and numrange cover time, date, integer and numeric intervals; tstzmultirange holds an ordered set of non-overlapping ranges. Discrete ranges canonicalise to the half-open [lower,upper) form PostgreSQL uses. Overlap, containment and adjacency are answered by functions such as range_overlaps(), range_contains() and numrange_contains_elem().
uuid and citext
The identifiers a distributed schema relies on. uuid stores sixteen bytes, accepts liberal input (hyphenated, unhyphenated or braced, any case), renders canonical lower-case, and generates fresh version-4 values with gen_random_uuid(). citext is case-insensitive text: it stores the original bytes and displays them as entered, folding case only for comparison, ordering and lookup — the natural type for e-mails, usernames and SKUs.
inet, cidr and macaddr
Network address types, IPv4 and IPv6 throughout. inet is a host with an optional netmask; cidr is a network with host bits zeroed; macaddr is a six-byte hardware address. Subnet containment is answered by network_sub() and its variants; inet_masklen(), inet_family(), inet_host() and inet_network() mirror their PostgreSQL equivalents for infrastructure tooling and security analytics.
One deliberate difference: predicates are functions
Informix extensibility predates PostgreSQL's modern extension APIs.
There is no CREATE OPERATOR for a user-defined type on
Informix — only a fixed set of named support functions binds to the
built-in operators. The relational operators =,
<, <=, >,
>= and <> do bind, so
comparison, sorting, ORDER BY, GROUP BY and
B-tree indexing read identically on both engines.
The symbolic range and network operators do not. PostgreSQL's
&&, @>, -|- and
<< are exposed on Informix as named functions
instead — range_overlaps(), range_contains(),
range_adjacent() and network_sub(). The
predicate is spelled differently; the result is the same. This is the
one line of a ported query that changes, and it is documented, not
hidden.
Informix extended types and the PostgreSQL equivalent
| PostgreSQL (native types) | Informix (ifxtools extended types) | |
|---|---|---|
| Type names and DDL | Native types declared directly in the schema. | ✓ The same type names and the same DDL — a PostgreSQL schema creates unchanged on Informix. |
| Canonical text | Produced by the native type. | ✓ Identical canonical text, verified three-way against Informix 14, Informix 15 and PostgreSQL. |
| Ordering and indexing | Native operators and B-tree indexing. | ✓ The compare function gives a total order; relational operators bind, so ORDER BY, GROUP BY and B-tree indexing read identically. |
| Overlap / containment / subnet | Symbolic operators: &&, @>, -|-, <<. | ✓ Named functions: range_overlaps(), range_contains(), range_adjacent(), network_sub() — Informix has no CREATE OPERATOR for a user-defined type. |
| Time zones | Full IANA time-zone database, with daylight-saving transitions. | ✓ Fixed numeric offsets, exact and dependency-free; zone-less input is interpreted as UTC and is deterministic. Named-zone support is a planned phase. |
| citext folding | Full Unicode folding by database locale. | ✓ ASCII case folding (A–Z); non-ASCII bytes compare verbatim. Exact for e-mail, username and SKU identifiers. |
| numrange precision | Arbitrary-precision numeric bounds; trailing scale preserved. | ✓ IEEE double bounds (roughly 15–16 significant digits); exact for the price and quantity bands it targets. |
| Where the type lives | Native engine type. | ✓ An opaque user-defined type compiled in C, in the engine, next to the data. |
Where the estate uses them
Each case below is a worked example in the paper, reproduced by the cross-engine test suite on live Informix 14, Informix 15 and PostgreSQL.
Event logs and audit timelines
An occurred_at timestamptz column stores every event as an absolute instant. Entries recorded in an India offset, a US Pacific offset and Zulu time all normalise to UTC and sort by the same instant, regardless of the zone they were entered in.
Booking and availability calendars
A tstzrange column holds each reservation; range_overlaps() finds every booking that collides with a proposed window, respecting inclusive and exclusive bounds. A tstzmultirange availability set merges overlapping and adjacent windows into one canonical form.
Contract terms and price bands
A daterange contract term canonicalises an inclusive end date to the exclusive next day, matching PostgreSQL's half-open form. An int8range quantity tier and a numrange price band answer element-containment questions such as whether a value falls inside a budget band.
Distributed keys and case-insensitive identifiers
A uuid primary key accepts any spelling and compares equal regardless of case or hyphenation. A citext e-mail column matches John@Example.com against john@example.com while displaying the address in its original case.
Device inventories and security analytics
An inet column stores host addresses in IPv4 and IPv6; network_sub() selects every device inside a given cidr subnet. The mask renders exactly as PostgreSQL's inet::text does, down to the /32 for a full host.
One schema across two engines
Because the type names and DDL are identical, a single schema can move between Informix and PostgreSQL unchanged. Only the operator or function spelling and the text-cast keyword (::text on PostgreSQL, ::lvarchar on Informix) differ.
Before you adopt the extended types.
Do these change the SQL my application already runs?
Comparison, sorting and indexing do not change: the relational operators (=, <, <=, >, >=, <>) bind to the types, so ORDER BY, GROUP BY and B-tree indexing read identically to PostgreSQL. What changes is the symbolic range and network predicates: Informix has no CREATE OPERATOR for a user-defined type, so PostgreSQL's &&, @>, -|- and << are exposed as named functions — range_overlaps(), range_contains(), range_adjacent() and network_sub(). The predicate is spelled differently; the result is the same.
Does a schema written for PostgreSQL run unchanged on Informix?
The DDL does: each type carries the same name, so CREATE TABLE statements and the canonical text of stored values are identical on both engines. In queries, only two things differ — the operator or function spelling for overlap, containment and subnet predicates, and the text-cast keyword (::text on PostgreSQL, ::lvarchar on Informix). Everything else, including sort order and indexing, is unchanged.
How do you know the Informix type behaves like PostgreSQL's?
Parity is tested, not asserted. Each type has one test class whose methods run once per engine — Informix 14, Informix 15 and PostgreSQL — and each assertion compares the result against an engine-independent reference, either a fixed canonical form or a computed value, rather than against whatever PostgreSQL happened to return. The suite runs 129 assertions green: canonical text, ordering, overlap and containment, subnet membership, epoch extraction, UUID generation and case-insensitive matching are all identical across the three engines.
Are these stored in the engine or handled in my application?
In the engine. Each type is an opaque user-defined type: a byte representation whose meaning is defined by C support functions registered in the engine. It stores, sorts, indexes, compares and casts like a first-class Informix type. The logic lives in the database, next to the data — not split across application code or hand-written constraints.
What about time zones and daylight saving?
The timestamptz type handles fixed numeric offsets, which is exact and dependency-free. Zone-less input is interpreted as UTC, so it is deterministic rather than dependent on a session setting. Named IANA zones with daylight-saving transitions require linking a time-zone database and are a deliberate future phase; the current scope is documented, and the tests pin PostgreSQL to UTC so the comparison is on equal footing.
Does citext fold case for non-ASCII text?
It folds ASCII case (A–Z); non-ASCII bytes compare verbatim. That is exact for the identifiers citext targets — e-mail addresses, usernames and SKUs. PostgreSQL folds full Unicode by database locale; the difference is documented, and it does not affect ASCII identifier matching.
Is numrange precise enough for money?
The numrange type uses IEEE double bounds, roughly 15–16 significant digits, which is exact for the price and quantity bands it is designed for; trailing-zero scale is not preserved. For scalar monetary values, Informix already ships MONEY and DECIMAL, which the extension deliberately does not re-implement — numrange exists to model bands and intervals, not to replace the native decimal types.
What does deploying the extension require?
A C compiler on the Informix server, a default sbspace present, and a logged database. Each type is compiled to a shared object and registered as a user-defined type with its support functions and casts. In this project the compile happens on the server itself: the install tooling streams the C source over the connection, compiles it in place, and runs the registration SQL. The default sbspace is required because that streaming step uses a temporary large-object table.