Common mistakes with numbers, dates and strings in Node.js + PostgreSQL Intermediate

This page answers: why an amount is off by 1 dong, an id is off by one, a report is off by a day. The culprit is usually not business logic but data types crossing the JS ↔ JSON ↔ PostgreSQL boundary.

Outputs in comments are real runs on Node 24.17, pg 8.23 and PostgreSQL 17, with the app at TZ=Asia/Ho_Chi_Minh unless noted otherwise.

Data types change shape at the boundary

PostgreSQL	node-postgres returns	Trap
bigint, count(*)	string	"1" + 1 === "11"
numeric	string	"12.50" + 1 === "12.501"
date	Date at 00:00 in the process’s timezone	JSON becomes 2026-10-17T17:00:00.000Z
timestamp	Date interpreted in the process’s timezone	Two servers in different TZs read two different instants
timestamptz	Date at the correct instant	Safe

Numbers

Computing money with floats

✕ Common

const total = items.reduce((sum, i) => sum + i.price, 0);
// 0.1 + 0.2              -> 0.30000000000000004
// adding 0.1 ten times   -> 0.9999999999999999
// (1.005).toFixed(2)     -> "1.00"  (expected "1.01")

✓ Prefer

// Store and compute in integers of the smallest unit (cents, or dong for VND)
const totalCents = items.reduce((sum, i) => sum + i.priceCents, 0);
// DB: amount_cents bigint, or numeric(14, 2) and let PostgreSQL compute the SUM

Why

number is a 64-bit binary float and can’t represent 0.1 exactly. Small errors accumulate across many rows, rounding goes wrong, and === comparisons with totals from the DB fail. End-of-day reconciliation being off by a few units is the classic symptom.

Believing count, bigint and numeric are numbers

✕ Common

const { rows } = await pool.query(
  'select count(*) as n, 12.50::numeric(12,2) as price',
);
rows[0].n + 1;      // "11"   (n is "1")
rows[0].price + 1;  // "12.501"

✓ Prefer

// Option 1: cast in SQL when you're sure it fits in int4
await pool.query('select count(*)::int as n');               // { n: 1 }

// Option 2: register a parser once at startup
import pg from 'pg';
pg.types.setTypeParser(pg.types.builtins.INT8, (v) => BigInt(v));
// count(*) -> 1n. numeric: keep it a string, compute with a decimal library or in SQL.

Why

node-postgres returns strings for types without a safe numeric parser: int8 can exceed Number.MAX_SAFE_INTEGER, and numeric can have arbitrary precision. The + operator on strings concatenates, so the mistake doesn’t throw; it produces a wrong number. TypeScript can’t save you because rows is any by default.

A bigint id beyond 2^53 turned into a number

✕ Common

JSON.parse('{"id":9007199254740993}').id; // 9007199254740992
Number('9007199254740993');               // 9007199254740992
// Calling GET /orders/9007199254740992 -> 404, or worse: hits a different order

✓ Prefer

// bigint ids travel through the API as strings; use BigInt for arithmetic
type OrderDto = { id: string };
BigInt('9007199254740993') + 1n; // 9007199254740994n
Number.isSafeInteger(2 ** 53 + 1); // false -> use it to reject in validation

Why

Integers are only exact up to Number.MAX_SAFE_INTEGER = 9007199254740991. Snowflake ids, ids from other systems, or long-running sequences can all exceed it. Converting to number anywhere (including JSON.parse on the client) makes the id silently change value.

parseInt and Number('') for query strings

✕ Common

const page = Number(req.query.page);   // ?page=   -> 0
const limit = parseInt(req.query.limit); // ?limit=10abc -> 10
['1', '2', '3'].map(parseInt);           // [ 1, NaN, NaN ]
// Number(''), Number(' '), Number(null) are all 0

✓ Prefer

function parsePositiveInt(raw: unknown, fallback: number, max: number): number {
  if (raw === undefined) return fallback;
  if (typeof raw !== 'string' || !/^\d+$/.test(raw)) throw new BadRequestException('Invalid number');
  const n = Number(raw);
  if (n < 1 || n > max) throw new BadRequestException(`Must be within 1..${max}`);
  return n;
}
// NestJS: ParseIntPipe or a DTO + class-validator instead of a hand-written function.

Why

• parseInt reads until the first non-digit and stops: "10px" → 10, "1e3" → 1.
• map(parseInt) passes the index as the radix, producing NaN.
• Number('') is 0, not NaN: an empty parameter slips past an isNaN check and becomes OFFSET 0 or LIMIT 0.

Using || for defaults

✕ Common

const limit = dto.limit || 20;              // limit: 0  -> 20
const name = dto.displayName || 'Anonymous'; // ''       -> 'Anonymous'
const discount = rule.percent || 10;         // 0%       -> 10% (!)

✓ Prefer

const limit = dto.limit ?? 20;              // 0  -> 0
const name = dto.displayName ?? 'Anonymous'; // '' -> ''
const discount = rule.percent ?? 10;         // 0  -> 0

Why

|| replaces every falsy value (0, '', false, NaN), while ?? only replaces null/undefined. For boolean flags and amounts, || turns “deliberately 0/false” into the default: a 0% discount becomes 10%, isActive: false becomes true.

Dates and times

new Date('YYYY-MM-DD') to filter by day

✕ Common

// ?from=2026-10-18 — "orders from the start of Oct 18"
const from = new Date(req.query.from);
from.getHours(); // 7 (TZ Asia/Ho_Chi_Minh) — misses orders from 00:00 to 07:00
// Server in America/New_York: from.getDate() -> 17

✓ Prefer

// State the business timezone explicitly, independent of the server's TZ
const from = new Date(`${req.query.from}T00:00:00+07:00`);
from.toISOString(); // 2026-10-17T17:00:00.000Z = 00:00 Vietnam time
// Or push it to PostgreSQL: where created_at >= ($1::date)::timestamp at time zone 'Asia/Ho_Chi_Minh'

Why

A date-only string is interpreted by JS as UTC midnight; a date-time string without an offset (2026-10-18T00:00) is interpreted as local time. The same code gives different results on a dev laptop, a TZ=UTC container, and a server in another region.

A timestamp (without timezone) column for event times

✕ Common

create table orders (id bigint, created_at timestamp);
-- The same Date 2026-10-18T02:00:00Z inserted from two pods:
--   pod TZ=Asia/Ho_Chi_Minh -> stores '2026-10-18 09:00:00'
--   pod TZ=UTC              -> stores '2026-10-18 02:00:00'

✓ Prefer

create table orders (id bigint, created_at timestamptz not null default now());
-- Both pods -> '2026-10-18 02:00:00+00' (the same instant)

Why

timestamp without time zone drops the offset when storing. node-postgres sends a Date in the process’s local time, so the stored value depends on the pod’s TZ.

timestamptz converts to UTC on storage, exactly what “when did this event happen” needs. Only use timestamp for “wall clock” times such as opening hours.

A date column turned into a Date by node-postgres

✕ Common

const { rows } = await pool.query("select '2026-10-18'::date as birthday");
res.json(rows[0]);
// {"birthday":"2026-10-17T17:00:00.000Z"} — the client shows Oct 17

✓ Prefer

import pg from 'pg';
pg.types.setTypeParser(pg.types.builtins.DATE, (v) => v); // keep the string as is
// -> {"birthday":"2026-10-18"}
// Or in SQL: select birthday::text

Why

date has no time, but node-postgres creates a Date at 00:00 in the process’s local time. At UTC+7, toJSON() subtracts 7 hours and the ISO string moves back to the previous day. Birthdays and expiry dates display one day off while the DB is still correct.

Arrays, objects, JSON

sort() without a compare function

✕ Common

[10, 9, 1, 100].sort();               // [ 1, 10, 100, 9 ]
['b', 'a', 'Ä', 'ä', 'C'].sort();     // [ 'C', 'a', 'b', 'Ä', 'ä' ]
const top = users.sort(byScore);      // also reorders the original users array

✓ Prefer

[10, 9, 1, 100].sort((a, b) => a - b);                 // [ 1, 9, 10, 100 ]
names.sort(new Intl.Collator('de').compare);           // [ 'a', 'ä', 'Ä', 'b', 'C' ]
const top = users.toSorted(byScore);                   // leaves the original untouched

Why

By default sort converts every element to a string and compares UTF-16 code units, so numbers and accented letters end up in the wrong order.

sort also mutates the array in place: sorting an array taken from a cache changes the cache for the next request too. toSorted (ES2023) returns a new array.

Shallow spread, then mutating nested objects

✕ Common

const DEFAULTS = { retry: { attempts: 3 }, tags: ['base'] };
function buildConfig(o: { attempts: number; tag: string }) {
  const c = { ...DEFAULTS };
  c.retry.attempts = o.attempts; // mutates the shared object
  c.tags.push(o.tag);
  return c;
}
buildConfig({ attempts: 10, tag: 'a' });
// DEFAULTS -> {"retry":{"attempts":10},"tags":["base","a"]}

✓ Prefer

function buildConfig(o: { attempts: number; tag: string }) {
  const c = structuredClone(DEFAULTS); // deep copy
  c.retry.attempts = o.attempts;
  c.tags.push(o.tag);
  return c;
}
// DEFAULTS stays {"retry":{"attempts":3},"tags":["base"]}

Why

Spread { ...obj } only copies one level; nested objects and arrays are still the same references. On a long-running server, each request gradually mutates the defaults: the bug only appears after hours of uptime and disappears on restart.

Trusting JSON.stringify to preserve data

✕ Common

JSON.stringify({ a: undefined, b: null, m: new Map([[1, 2]]), n: NaN });
// {"b":null,"m":{},"n":null}   — a is gone, the Map is empty, NaN became null
JSON.stringify({ id: 1n });
// TypeError: Do not know how to serialize a BigInt

✓ Prefer

const body = JSON.stringify(
  { id: 1n, tags: Object.fromEntries(map) },
  (_key, v) => (typeof v === 'bigint' ? v.toString() : v),
);
// {"id":"1", ...}
// PATCH: use null for "clear the value", because undefined vanishes from the payload.

Why

• undefined and functions drop the key; Map/Set become {}; NaN/Infinity become null.
• BigInt throws: enable an INT8 → BigInt parser but forget the serializer, and every response with a count returns 500.

Checking string length with .length

✕ Common

'👍'.length;                 // 2
if (name.length > 5) throw new BadRequestException(); // 'abcd👍' is rejected (length 6)
'Hello 👍'.slice(0, 7);      // "Hello \ud83d" — splits the emoji in half, a broken character

✓ Prefer

[...'abcd👍'].length;        // 5 — counts code points, matches PostgreSQL's length()
const seg = new Intl.Segmenter('en', { granularity: 'grapheme' });
[...seg.segment('👨‍👩‍👧')].length; // 1 — the number of "characters" the user sees
// Normalize before comparing/storing unique values: s.normalize('NFC')

Why

• .length counts UTF-16 code units; an emoji takes 2. PostgreSQL varchar(5) counts characters, so it accepts 'abcd👍' while the API rejects it.
• Accented text has two forms: 'Zürich' is length 6 in NFC and 7 in NFD, and 'ü' === 'ü' is false. Without normalize('NFC'), a unique username constraint can be bypassed.

Quick checklist

• Money: integers of the smallest unit or numeric, never floats.
• A decision on how node-postgres parses int8/numeric/date, applied app-wide.
• bigint ids travel through the API as strings.
• Numeric query params are validated strictly (regex/ParseIntPipe/DTO), not bare parseInt.
• Defaults use ??, not ||.
• Every date string has an explicit offset; event-time columns are timestamptz.
• sort has a compare function; shared data isn’t sorted/spread and then mutated.
• The serializer handles BigInt; PATCH distinguishes null from a missing key.
• Length limits match how PostgreSQL counts; user-entered strings are normalize('NFC')d.

Next: Database.