Problem with inserting big numbers (more than 64bit)

[Mouradif]

Consider the following PostgreSQL table:

CREATE TABLE scratchpad (
	n NUMERIC(78, 0)
)

I can run this query without any problem:

INSERT INTO scratchpad
VALUES (57896044618658097711785492504343953926634992332820282019728792003956564819968)

Also works with

INSERT INTO scratchpad
VALUES ('57896044618658097711785492504343953926634992332820282019728792003956564819968')

Or

INSERT INTO scratchpad
VALUES (NUMERIC '57896044618658097711785492504343953926634992332820282019728792003956564819968')

but if I try this with Postgres-JS:

main.js

require('dotenv').config();
const postgres = require('postgres');

const sql = postgres({ debug: true });

async function main() {
  const bn = 1n << 255n;
  await sql`INSERT INTO scratchpad (n) VALUES (${bn})`;
}

main().catch(console.error);

$ node main.js
PostgresError: value "57896044618658097711785492504343953926634992332820282019728792003956564819968" is out of range for type bigint
    at ErrorResponse (/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
    at new Query (/app/node_modules/postgres/cjs/src/query.js:35:9)
    at sql (/app/node_modules/postgres/cjs/src/index.js:112:11)
    at main (/app/main.js:10:12)
    at Object.<anonymous> (/app/main.js:13:1)
    at Module._compile (node:internal/modules/cjs/loader:1358:14)
    at Module._extensions..js (node:internal/modules/cjs/loader:1416:10)
    at Module.load (node:internal/modules/cjs/loader:1208:32)
    at Module._load (node:internal/modules/cjs/loader:1024:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:174:12)
    at node:internal/main/run_main_module:28:49 {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '22003',
  where: "unnamed portal parameter $1 = '...'",
  file: 'numutils.c',
  line: '873',
  routine: 'pg_strtoint64_safe',
  query: 'INSERT INTO scratchpad (n) VALUES ($1)',
  parameters: [
    '57896044618658097711785492504343953926634992332820282019728792003956564819968'
  ],
  args: [
    57896044618658097711785492504343953926634992332820282019728792003956564819968n
  ],
  types: [ 20 ]
}

Looking at the stack trace, the error seems to be coming from the postgreSQL server itself (since I see a TCP.onStreamRead in the stack). My guess is when preparing the statement, the wrong type is passed.

[porsager]

Maybe you didn't see the right part at the top of the error? It says:

value "number here" is out of range for type bigint