int128 adds a native 128-bit signed integer type to PostgreSQL 17, 18 and 19.
It stores values from -170141183460469231731687303715884105728 to
170141183460469231731687303715884105727. It is implemented in C on top of
the compiler's __int128 type, with checked arithmetic, full text and binary
I/O, casts to and from the standard numeric types, and operator classes for
the btree, hash, GiST, SP-GiST, GIN and BRIN access methods.
The type is intended for identifiers, counters, monetary amounts in minor
units, hashes, and any other value that outgrows bigint but does not need
the arbitrary precision (and per-value overhead) of numeric.
CREATE EXTENSION int128;
CREATE TABLE ledger (
id int128 PRIMARY KEY,
amount int128 NOT NULL
);
INSERT INTO ledger VALUES
(1, 170141183460469231731687303715884105727),
(2, -170141183460469231731687303715884105728);
SELECT sum(amount) FROM ledger;- Features
- Requirements
- Installation
- The type
- Value range
- Operators
- Functions
- Mathematical functions
- Casts
- Index support
- Aggregates
- Window functions
- Range types
- Performance notes
- Limitations
- Regression tests
- License
- Signed 128-bit integer with the range
-170141183460469231731687303715884105728to170141183460469231731687303715884105727. - Overflow-checked arithmetic:
+,-,*,/,%, unary-,abs(),gcd()andlcm()raiseint128 out of rangeinstead of wrapping. - Bitwise operators
&,|,#(XOR),~,<<and>>. - Text and binary I/O, so
COPY ... WITH (FORMAT binary)and client binary protocols work. - Implicit casts from
smallint,integerandbigint; assignment casts fromnumeric,realanddouble precision; implicit casts tonumeric,realanddouble precision; range-checked assignment casts to the smaller integer types. - btree, hash, GiST, SP-GiST, GIN and BRIN operator classes, including BRIN
minmaxandminmax-multi. - btree skip-scan support on PostgreSQL 18 and later.
- GiST support for exclusion constraints and index-only scans.
- SP-GiST support using a balanced binary search tree over the 128-bit value.
- GIN support for equality,
= ANY(array)and multicolumn GIN indexes. sum(),avg(),min()andmax()aggregates, all parallel safe.- Mathematical functions: rounding, powers, roots, logarithms,
trigonometry,
bit_count(),width_bucket(),random()andto_char(). - Statistical aggregates:
stddev_samp(),stddev_pop(),var_samp()andvar_pop(). - Bitwise aggregates:
bit_and(),bit_or()andbit_xor(). generate_series(),scale(),min_scale(),trim_scale(),int128_inc(),int128_dec()andpg_size_pretty().int128rangeandint128multirangerange types, including GiST-indexed range columns.- GiST and SP-GiST KNN ordering through the
<->operator. - Sort support, btree deduplication and
in_rangesupport for window functions. - Works as a primary key, unique key, foreign key, hash/range partition key, hash join key and merge join key.
- PostgreSQL 17, 18 or 19.
- A 64-bit platform with a compiler that provides native 128-bit integers
(
__int128). GCC and Clang on x86-64 and AArch64 qualify. The build fails with a clear error if the compiler does not support the type. - PostgreSQL server development headers (
postgresql-server-dev-*or a source/pg_configinstallation).
Build and install with PGXS:
make PG_CONFIG=/path/to/pg_config
make install PG_CONFIG=/path/to/pg_configThen, in each database that needs the type:
CREATE EXTENSION int128;The extension is relocatable, so it can be installed into any schema:
CREATE SCHEMA types;
CREATE EXTENSION int128 SCHEMA types;| Property | Value |
|---|---|
| SQL name | int128 |
| Range | -170141183460469231731687303715884105728 to 170141183460469231731687303715884105727 |
| Internal length | 16 bytes, fixed |
| Passed by value | no |
| Alignment | double (8 bytes) |
| Storage | plain (never toasted) |
| Category | N (numeric) |
| Typmod | none |
| Value | |
|---|---|
| Minimum | -170141183460469231731687303715884105728 |
| Maximum | 170141183460469231731687303715884105727 |
| Bits | 128, signed two's complement |
| Decimal digits | 39 (maximum) |
The minimum is -2^127; the maximum is 2^127 - 1. Values outside this
range are rejected on input, and arithmetic that would leave the range raises
int128 out of range:
SELECT '170141183460469231731687303715884105727'::int128; -- maximum
SELECT '-170141183460469231731687303715884105728'::int128; -- minimum
SELECT '170141183460469231731687303715884105728'::int128; -- errorValues are written as ordinary base-10 integers with an optional sign.
Leading and trailing whitespace is accepted, matching bigint input:
SELECT '42'::int128, ' -42 '::int128, '+7'::int128;Out-of-range input is rejected:
SELECT '170141183460469231731687303715884105728'::int128;
ERROR: value "170141183460469231731687303715884105728" is out of range for type int128Because the type is 16 bytes wide, PostgreSQL stores a pointer to the value
in the tuple rather than the value itself. This is inherent: Datum is one
machine word, and PostgreSQL only allows typlen 1, 2, 4 or 8 for
pass-by-value types.
| Operator | Left | Right | Result | Description |
|---|---|---|---|---|
= |
int128 |
int128 |
boolean |
equal |
<> |
int128 |
int128 |
boolean |
not equal |
< |
int128 |
int128 |
boolean |
less than |
<= |
int128 |
int128 |
boolean |
less than or equal |
> |
int128 |
int128 |
boolean |
greater than |
>= |
int128 |
int128 |
boolean |
greater than or equal |
+ |
int128 |
int128 |
int128 |
addition |
- |
int128 |
int128 |
int128 |
subtraction |
* |
int128 |
int128 |
int128 |
multiplication |
/ |
int128 |
int128 |
int128 |
division, truncating toward zero |
% |
int128 |
int128 |
int128 |
remainder |
- |
— | int128 |
int128 |
negation |
@ |
— | int128 |
int128 |
absolute value |
& |
int128 |
int128 |
int128 |
bitwise AND |
| |
int128 |
int128 |
int128 |
bitwise OR |
# |
int128 |
int128 |
int128 |
bitwise XOR |
~ |
— | int128 |
int128 |
bitwise NOT |
<< |
int128 |
integer |
int128 |
left shift |
>> |
int128 |
integer |
int128 |
arithmetic right shift |
The comparison operators carry HASHES and MERGES, so hash joins and merge
joins are available. Selectivity is estimated with the standard scalar
selectivity functions.
Shift semantics are fully defined, unlike the platform-dependent behaviour of
out-of-range shifts on bigint:
- A negative shift count reverses the direction (
x << -3isx >> 3). - A left shift of 128 or more bits yields
0. - A right shift of 128 or more bits yields
0for non-negative values and-1for negative values.
| Function | Result | Description |
|---|---|---|
abs(int128) |
int128 |
absolute value |
div(int128, int128) |
int128 |
truncating division |
mod(int128, int128) |
int128 |
remainder |
gcd(int128, int128) |
int128 |
greatest common divisor |
lcm(int128, int128) |
int128 |
least common multiple |
int128_cmp(int128, int128) |
integer |
btree comparator (-1, 0, 1) |
int128_hash(int128) |
integer |
hash support function |
int128_hash_extended(int128, bigint) |
bigint |
seeded hash support function |
int128_send(int128) |
bytea |
binary output |
int128_recv(internal) |
int128 |
binary input |
int128_in(cstring) |
int128 |
text input |
int128_out(int128) |
cstring |
text output |
int128_sortsupport(internal) |
void |
btree sort support |
int128_in_range(int128, int128, int128, boolean, boolean) |
boolean |
window frame support |
int128range_subdiff(int128, int128) |
double precision |
subtype_diff for range types |
generate_series(int128, int128) |
setof int128 |
inclusive series |
generate_series(int128, int128, int128) |
setof int128 |
inclusive series with step |
scale(int128) |
integer |
always 0 |
min_scale(int128) |
integer |
always 0 |
trim_scale(int128) |
int128 |
identity |
int128_inc(int128) |
int128 |
add one |
int128_dec(int128) |
int128 |
subtract one |
pg_size_pretty(int128) |
text |
human-readable size |
width_bucket(int128, int128[]) |
integer |
array-threshold form |
int128_dist(int128, int128) |
int128 |
absolute difference, used by <-> |
gcd() and lcm() follow the same conventions as their bigint
counterparts: gcd(x, 0) = abs(x), gcd(0, 0) = 0, and lcm(x, 0) = 0.
Results that cannot be represented raise int128 out of range.
All functions are IMMUTABLE and PARALLEL SAFE except random(), which is
VOLATILE PARALLEL RESTRICTED. Algebraic functions return numeric for
exactness; transcendental functions return double precision, matching
PostgreSQL's numeric and float8 conventions.
| Function | Returns | Notes |
|---|---|---|
round(int128) |
int128 |
identity |
round(int128, int) |
int128 |
rounds to 10^-n for negative n; half away from zero |
trunc(int128) |
int128 |
identity |
trunc(int128, int) |
int128 |
truncates to 10^-n for negative n |
ceil(int128) |
int128 |
identity |
ceiling(int128) |
int128 |
identity |
floor(int128) |
int128 |
identity |
sign(int128) |
int128 |
-1, 0 or 1 |
power(int128, int128) |
numeric |
|
pow(int128, int128) |
numeric |
alias for power |
sqrt(int128) |
numeric |
|
cbrt(int128) |
float8 |
|
exp(int128) |
numeric |
|
ln(int128) |
numeric |
|
log(int128) |
numeric |
base 10 |
log10(int128) |
numeric |
base 10 |
log(int128, int128) |
numeric |
log(base, value) |
factorial(int128) |
numeric |
errors for negative input |
sin(int128) |
float8 |
|
cos(int128) |
float8 |
|
tan(int128) |
float8 |
|
cot(int128) |
float8 |
|
asin(int128) |
float8 |
|
acos(int128) |
float8 |
|
atan(int128) |
float8 |
|
atan2(int128, int128) |
float8 |
atan2(y, x) |
sinh(int128) |
float8 |
|
cosh(int128) |
float8 |
|
tanh(int128) |
float8 |
|
asinh(int128) |
float8 |
|
acosh(int128) |
float8 |
|
atanh(int128) |
float8 |
|
degrees(int128) |
float8 |
|
radians(int128) |
float8 |
|
bit_count(int128) |
int4 |
population count |
width_bucket(int128, int128, int128, int) |
int4 |
|
random(int128, int128) |
int128 |
uniform in [min, max] |
to_char(int128, text) |
text |
delegates to to_char(numeric, text) |
SELECT round(12345::int128, -2), sqrt(144::int128), cos(0::int128);
round | sqrt | cos
-------+------+-----
12300 | 12 | 1| From | To | Context | Notes |
|---|---|---|---|
smallint |
int128 |
implicit | lossless |
integer |
int128 |
implicit | lossless |
bigint |
int128 |
implicit | lossless |
numeric |
int128 |
assignment | rounds half away from zero; range checked |
real |
int128 |
assignment | uses rint(); range checked |
double precision |
int128 |
assignment | uses rint(); range checked |
int128 |
smallint |
assignment | range checked |
int128 |
integer |
assignment | range checked |
int128 |
bigint |
assignment | range checked |
int128 |
numeric |
implicit | exact |
int128 |
real |
implicit | may lose precision |
int128 |
double precision |
implicit | may lose precision |
text |
int128 |
explicit (I/O) | parses the text representation |
int128 |
text |
explicit (I/O) | formats the value |
The rounding rules match PostgreSQL's existing bigint casts:
numeric rounds half away from zero (2.5 becomes 3), while floating
point uses rint() and therefore round-half-to-even (2.5 becomes 2).
SELECT 2.5::numeric::int128, 2.5::float8::int128;
int128 | int128
--------+--------
3 | 2Supports =, <>, <, <=, >, >=, ORDER BY, DISTINCT, GROUP BY,
IN, BETWEEN, unique constraints, primary keys, foreign keys, range
partitioning, merge joins and the min()/max() index optimisation. On
PostgreSQL 18 and later it also provides skip-scan support for multicolumn
indexes.
CREATE INDEX ledger_id_idx ON ledger (id);
SELECT * FROM ledger WHERE id = '500000000000000000000'::int128;The operator class provides sort support (avoiding per-comparison fmgr
overhead), in_range support for RANGE window frames, and
btequalimage so btree deduplication can be used.
Supports equality lookups, hash joins, hash aggregation and hash partitioning.
CREATE INDEX ledger_id_hash ON ledger USING hash (id);The GiST operator class stores a bounding box [lower, upper] per subtree,
so it supports the same comparison operators as btree, plus exclusion
constraints and index-only scans (through the fetch support function).
CREATE INDEX ledger_id_gist ON ledger USING gist (id);
-- exclusion constraint: no two rows may share an id
ALTER TABLE ledger ADD CONSTRAINT ledger_id_excl
EXCLUDE USING gist (id WITH =);GiST is useful when you need an exclusion constraint over int128, or when
you want a single index type that can be combined with other GiST-indexed
columns in a multicolumn exclusion constraint. For plain lookups and range
scans, btree is smaller and faster.
KNN ordering (ORDER BY id <-> value) is supported through the <->
operator and the int128_gist_distance() support function. The distance is
returned as double precision, so ordering is approximate for values beyond
2^53.
The SP-GiST operator class is a one-dimensional binary search tree. Each inner tuple stores a pivot (the median of the values being split) and has up to two nodes: values below the pivot and values at or above it. This gives logarithmic search for equality and range predicates, and avoids the page splits of a btree when values arrive in random order.
CREATE INDEX ledger_id_spgist ON ledger USING spgist (id);SP-GiST is a good choice for read-mostly tables with random insert order.
It does not support exclusion constraints or index-only scans. KNN ordering
(ORDER BY id <-> value) is supported through the <-> operator.
The GIN operator class stores each value as a single key, so equality
lookups and = ANY(array) are exact. Range operators use a partial-match
scan over the ordered key space; this is correct but usually slower than
btree, so GIN is mainly useful for multicolumn GIN indexes that mix
int128 with array or jsonb columns.
CREATE INDEX ledger_id_gin ON ledger USING gin (id);
-- multicolumn GIN: int128 plus an array column
CREATE INDEX ledger_gin ON ledger USING gin (id, tags);GIN does not support <>; use btree, GiST or SP-GiST for that.
Both BRIN operator classes store 16-byte values directly. minmax keeps one
minimum and one maximum per range; minmax-multi keeps a bounded set of
values per range and is more robust when the data is not physically sorted.
CREATE INDEX ledger_id_brin ON ledger USING brin (id)
WITH (pages_per_range = 32);
CREATE INDEX ledger_id_brin_multi ON ledger
USING brin (id int128_minmax_multi_ops)
WITH (pages_per_range = 32);BRIN is a good fit for append-only tables with naturally increasing keys, where it is orders of magnitude smaller than a btree index.
GIN and BRIN do not support KNN ordering.
| Aggregate | Result | Notes |
|---|---|---|
sum(int128) |
int128 |
overflow checked; parallel safe |
avg(int128) |
numeric |
exact; parallel safe |
min(int128) |
int128 |
uses the btree opclass; parallel safe |
max(int128) |
int128 |
uses the btree opclass; parallel safe |
stddev_samp(int128) |
numeric |
sample standard deviation; parallel safe |
stddev_pop(int128) |
numeric |
population standard deviation; parallel safe |
var_samp(int128) |
numeric |
sample variance; parallel safe |
var_pop(int128) |
numeric |
population variance; parallel safe |
bit_and(int128) |
int128 |
bitwise AND; parallel safe |
bit_or(int128) |
int128 |
bitwise OR; parallel safe |
bit_xor(int128) |
int128 |
bitwise XOR; parallel safe |
sum() updates its transition value in place when running as an aggregate,
so it does not allocate one value per input row. avg() accumulates an exact
128-bit sum and a row count, then divides as numeric, so the result is
exact even for values beyond the range of double precision.
SELECT sum(amount), avg(amount), min(amount), max(amount) FROM ledger;The btree operator class provides in_range support, so RANGE frames with
int128 offsets work:
SELECT id,
count(*) OVER (ORDER BY id RANGE BETWEEN 100 PRECEDING AND CURRENT ROW)
FROM ledger;The extension provides int128range and int128multirange, built on
int128range_subdiff():
SELECT '[1,10)'::int128range @> 5::int128;
SELECT '{[1,10)}'::int128multirange;
CREATE TABLE windows (span int128range);
CREATE INDEX windows_span_idx ON windows USING gist (span);
SELECT * FROM windows WHERE span && '[100,200)'::int128range;- Arithmetic uses native
__int128operations. Overflow is detected with__builtin_add_overflow,__builtin_sub_overflowand__builtin_mul_overflow, which compile to a single flag-setting instruction rather than a branchy pre-check. - Values are loaded and stored with
memcpy(). PostgreSQL has no 16-bytetypalign, so a value in a tuple may be only 8-byte aligned; casting the pointer toint128 *would let the compiler emit aligned 16-byte loads that fault on strict-alignment targets. Thememcpy()compiles to two 64-bit moves. - The btree comparator and sort support function avoid fmgr call overhead during sorting.
sum()mutates its transition value in place.- Text output formats directly into a stack buffer; no
snprintfwithlong longpairs is involved. - The type is pass-by-reference, so each value costs a pointer indirection
and 16 bytes of storage. If your values fit in
bigint,bigintremains faster and smaller.
-
No typmod support (
int128(10)is not a thing). -
GiST and SP-GiST KNN ordering use
double precisiondistances, so they are approximate for values beyond 2^53. -
GIN range scans are correct but not selective; prefer btree for range predicates. GIN does not support
<>. -
Integer literals larger than
bigintare typed asnumericby the parser. A comparison such asWHERE id = 500000000000000000000therefore becomes anumericcomparison and cannot use anint128index. Cast the literal explicitly:SELECT * FROM ledger WHERE id = '500000000000000000000'::int128;
This is the same behaviour PostgreSQL has for
bigintcolumns compared with out-of-range literals. -
Other
numericformatting functions (for exampleto_number()) require an explicit cast tonumeric. -
to_jsonb()androw_to_json()renderint128values as JSON strings, because PostgreSQL's JSON categorisation only recognises the built-in numeric types. Cast tonumericfor a JSON number:to_jsonb(v::numeric).
The extension ships with a pg_regress suite covering I/O, arithmetic, overflow, bitwise operations, comparisons, hashing, binary output, casts, aggregates, window frames, range types, type integration, opclass validation, mathematical functions, and the btree, hash, GiST, SP-GiST, GIN and BRIN operator classes. The suite is split into seven files:
| File | Coverage |
|---|---|
int128 |
I/O, arithmetic, bitwise, comparisons, hashing, binary output, aggregates, window frames, type integration, opclass validation, range types |
int128_casts |
Every cast in both directions, rounding and range errors |
int128_index |
btree, hash, BRIN minmax and minmax-multi, joins, primary/foreign keys, partitioning, btree skip support |
int128_gist |
GiST scans, index-only scans, KNN ordering, exclusion constraints, extreme values |
int128_spgist |
SP-GiST scans, KNN ordering, extreme values, all-same split path |
int128_gin |
GIN equality, ranges, = ANY, pending list, multicolumn GIN |
int128_math |
Rounding, powers, roots, logs, trigonometry, bit_count, width_bucket (both forms), random, to_char, generate_series, scale, int128_inc/dec, pg_size_pretty, int128_dist, statistical aggregates |
With a running server and the extension installed:
make installcheck PG_CONFIG=/path/to/pg_configThe suite creates and drops its own tables in the contrib_regression
database.
This extension is distributed under the PostgreSQL License, the same
license as PostgreSQL itself. The full text is in the LICENSE file.
Implemented by Hans-Jürgen Schönig, [email protected]
CYBERTEC PostgreSQL International GmbH — https://www.cybertec-postgresql.com