It is a brief explanation of why int8
data type in PostgreSQL is converted to string
instead of number
in Node.JS. tl;dr it is a feature, not bug.
But First, Little Story
It can happen to anyone. Business requirement stated a transaction may be worth many billions in certain currencies. So, I needed to update SQL column data type from int4
to int8
. That was it? I supposed so. Turned out not. People were furious and they had thrown me into dark pit of debugging.
I used Typescript but somehow it slipped through. I realized when taking a look on log and I saw long string like 8233800739007328003173000
. It's enough clue that number addition became string concatenation. The returned query turned out as string.
But how?
I was baffled for few moments, then regained composure. The chaos was fixed by casting those string
back to number
. After that, business flows as usual, people cheered, and I reclaimed my state of sanity.
Why It's There, and Why It's Not A Bug
The question lingers, though? Why did it turned into string? I did a little research and found out that it is expected behaviour. Yes, PostgreSQL int8
will always be converted to Node.JS string
. The culprit here is package called node-postgres or pg
.
If you work with PostgreSQL in Node.JS, chances are there's pg
in your node_modules
. That's one of the packages that hold Node.JS community in one piece. Yet, exactly in this package the decision above was made. It is designed to handle one specific problem: Javascript number
. Now, let me explain a bit on number
before coming back.
Javascript number
data type is 64-bit. Meanwhile, PostgreSQL int8
is also 64-bit. However, they are different. The first one is IEEE 754 double precision, while the later is signed integer. Javascript number
has bits dedicated for precision and sacrifices range. Now let's compare.
- max value of Javascript
number
(orNumber.MAX_SAFE_INTEGER
) is2^53 - 1
or 9.007.199.254.740.991. - max value of PostgreSQL
int8
is2^63 - 1
or 9.223.372.036.854.775.807
Then, what will happen when the stored value in PostgreSQL is bigger than Javascript number
max value? Javascript will read the binary representation and churn out incorrect value.
That is problem. BIG problem.
That's where pg
package comes in and saves the day. The author recognized this problem, then decided to cast the result as Javascript string
, which does not alter the output value.
For me it is beautiful trade-off. First, it handles all cases. Second, it avoids worst output. Third, it gives simple resolve once the value is safe in Javascript.
So, hats-off for Brian C, the author of pg
.
Top comments (0)