Porting UNSIGNED Columns: MySQL’s Extra Bit and PostgreSQL’s Missing Unsigned Types

The analytics pipeline assigned every tracked entity a 64-bit hash, stored in a MySQL column declared BIGINT UNSIGNED, and for three years that column quietly held values across the full range — including the half above 9,223,372,036,854,775,807 that a signed 64-bit integer cannot represent. When the service moved to PostgreSQL 16, the schema tool mapped BIGINT UNSIGNED to bigint, which looks correct in a diff and is wrong by a factor of two: PostgreSQL has no unsigned integer types, and bigint tops out at 2^63 – 1. The first sync batch inserted fine for about fifty percent of rows and then began failing with ERROR: bigint out of range, one statement at a time, each failure aborting its transaction — a failure mode whose transaction-poisoning mechanics are their own story, covered in the savepoints comparison. The pipeline spent a night stuck at row 3.2 million of 6.4 million, and the fix was a column-type decision we should have made at schema-review time.

UNSIGNED is one of those MySQL features that feels like a small annotation and turns out to carry three separate behaviors: a doubled positive range, a rejection of negative input, and an arithmetic mode that silently wraps. PostgreSQL offers none of them directly. This article is the field guide: what UNSIGNED actually does, the subtraction wraparound that has burned more people than the range itself, and the mapping matrix that survived our migration.

What does UNSIGNED actually do in MySQL?

It shifts the representable range instead of extending it: INT UNSIGNED runs from 0 to 4,294,967,295 instead of roughly negative to positive 2.1 billion, and BIGINT UNSIGNED from 0 to 18,446,744,073,709,551,615. On input, behavior depends on SQL mode — with strict mode, inserting a negative value into an unsigned column errors; without it, MySQL historically clamped the value to 0 with a warning, and applications were built on both contracts. The strictness spectrum and what changed across 5.7 and 8.0 is covered in the SQL mode versus strictness notes; the short version is that two MySQL servers with different sql_mode settings can disagree about what the same INSERT does to an unsigned column.

The behavior that produces the most spectacular bugs, though, is arithmetic: MySQL evaluates subtraction between unsigned integers in unsigned arithmetic, so subtracting a larger unsigned from a smaller one wraps around to an astronomically large value instead of going negative — unless NO_UNSIGNED_SUBTRACTION is set, in which case the result is signed. A usage-billing query that computed remaining_quota = quota – used went from reporting a customer at negative forty units (a real bug worth paging on) to reporting them at 18.4 quintillion units (a silent accounting catastrophe), purely because a schema cleanup had made both columns UNSIGNED. We now grep migrations for UNSIGNED on any column that participates in subtraction, full stop:

-- MySQL: the wraparound that strict mode does NOT save you from
SELECT CAST(5 AS UNSIGNED) - CAST(9 AS UNSIGNED);
-- 18446744073709551612 unless NO_UNSIGNED_SUBTRACTION is set

-- audit: every unsigned integer column in the schema
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'appdb' AND column_type LIKE '%unsigned';

What are the PostgreSQL mapping options?

Four, in order of how often we actually chose them. First, plain bigint: correct whenever the real data never crosses 2^63 – 1, which covers auto-generated ids on any table whose insert rate is physically possible — the AUTO_INCREMENT versus identity notes do that arithmetic. Second, numeric(20,0): the honest representation of the full BIGINT UNSIGNED range, at the cost of slower arithmetic and larger storage — this was the hash column’s destination, because the hashes genuinely used the high half. Third, a smaller signed type with a CHECK (col >= 0) constraint, which preserves the rejects-negatives contract when the range shift was never needed — in our audit, most UNSIGNED columns existed out of habit, not range requirements, and int with a check constraint was the truthful port. Fourth, a domain: CREATE DOMAIN uint4 AS integer CHECK (VALUE >= 0) centralizes the pattern when dozens of columns share it, and behaves like a type in casts and error messages.

One trap to name: client libraries. Several drivers expose MySQL’s BIGINT UNSIGNED as an unsigned 64-bit value, and application code had grown to depend on that — our Go service used uint64, which happily round-trips the high half. After the numeric(20,0) mapping, the driver returned those values as strings, and two services silently parsed them with a signed 64-bit parser that failed on exactly the same values the database had rejected a month earlier. The type mapping is not done until the driver boundary agrees with it; the ORM behavior changes notes cover the wider family of these mismatches.

What does the pre-migration audit look like?

Before choosing any mapping, measure the data: the maximum value in every unsigned column decides which option is honest, and a five-minute query beats a schema-tool default. We ran MAX() per unsigned column across the fleet, compared against the signed ceiling, and found exactly one column family — the hashes — that needed numeric; everything else mapped to bigint or integer with a check constraint. The audit query above lists the columns; per-column maxima come from generated SELECT statements against the same information_schema list. Two more audit items from the incident report: verify no application code subtracts on the column, because the wraparound contract dies in the port regardless of type choice; and check whether any external system — an API client, a data warehouse, a CSV export consumer — parses the column as unsigned, because the wire format changes with the type. Type decisions are schema decisions plus client decisions, and the unsigned port fails at the boundary nobody reviewed.

Where MonPG fits when a type decision goes wrong

Out-of-range inserts are loud at the statement level and quiet at the system level: the pipeline’s retry loop hid them for hours while the table fell further behind. MonPG’s PostgreSQL monitoring surfaces statement errors alongside latency and throughput, so a sync job whose error rate jumped from zero to fifty percent at 02:00 appears as exactly that, with the failing statement attached, instead of as a slowly-growing lag graph someone notices at standup.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will watch: the wraparound arithmetic and truncation warnings that MySQL raises without failing. Until then, the MySQL monitoring page tracks that work. The portable lesson fits in one line: UNSIGNED is three features wearing one keyword, and a faithful port has to decide about range, negative rejection, and arithmetic separately — the schema diff will not do it for you.

Related documentation

• MySQL: numeric types
• PostgreSQL: numeric types