{"id":11549,"library":"pg-types","title":"PostgreSQL Type Converters","description":"pg-types is a core utility package designed to handle query result type conversions specifically for `node-postgres`, the popular PostgreSQL client for Node.js. It addresses the fundamental challenge of reconciling PostgreSQL's rich type system with JavaScript's more limited native types. By default, PostgreSQL servers return all data as strings. `node-postgres` then converts these into appropriate JavaScript types like `Date` or `Number` where possible. `pg-types` allows developers to override these default parsing behaviors for specific PostgreSQL OIDs (Object IDs). This is crucial for handling types like 64-bit integers (`int8`), which JavaScript cannot represent precisely without losing precision, or for integrating with custom data types. The current stable version is 4.1.0, with releases typically synchronized with its parent `node-postgres` project. Its key differentiator is the fine-grained control it offers over type parsing, enabling precise data handling and preventing potential data loss or incorrect interpretations, particularly with large numbers or complex date/time types. The package also ships with TypeScript definitions, enhancing developer experience for type-safe applications.","status":"active","version":"4.1.0","language":"javascript","source_language":"en","source_url":"git://github.com/brianc/node-pg-types","tags":["javascript","postgres","PostgreSQL","pg","typescript"],"install":[{"cmd":"npm install pg-types","lang":"bash","label":"npm"},{"cmd":"yarn add pg-types","lang":"bash","label":"yarn"},{"cmd":"pnpm add pg-types","lang":"bash","label":"pnpm"}],"dependencies":[],"imports":[{"note":"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.","wrong":"import * as types from 'pg-types'; // While possible, typically accessed via 'pg' module.","symbol":"types","correct":"import { types } from 'pg';"},{"note":"For CommonJS environments, the `types` object is retrieved as a named export from the `pg` module.","wrong":"const types = require('pg-types'); // Direct require is less common for runtime modification.","symbol":"types","correct":"const { types } = require('pg');"},{"note":"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.","wrong":"import { TypeParser } from 'pg-types'; // Incorrect for type-only import","symbol":"TypeParser","correct":"import type { TypeParser } from 'pg-types';"}],"quickstart":{"code":"import { Client, types } from 'pg';\nimport moment from 'moment'; // Example dependency for custom date parsing\n\nasync function runPgTypeParsersExample() {\n  // PostgreSQL OID for BIGINT (int8) is 20.\n  // By default, node-postgres returns int8 as string to prevent overflow.\n  // Override to parse as JavaScript Number, *only if* confident values won't exceed Number.MAX_SAFE_INTEGER.\n  types.setTypeParser(20, (val: string) => {\n    // null values are never parsed by default.\n    return val === null ? null : parseInt(val, 10);\n  });\n\n  // PostgreSQL OID for TIMESTAMPTZ (timestamp with time zone) is 1184.\n  // Override to parse as moment objects for custom date handling.\n  // Requires 'moment' to be installed (npm install moment).\n  types.setTypeParser(types.builtins.TIMESTAMPTZ, (val: string) => {\n    return val === null ? null : moment(val);\n  });\n\n  // Example usage with a pg client\n  const client = new Client({\n    user: process.env.PGUSER ?? 'postgres',\n    host: process.env.PGHOST ?? 'localhost',\n    database: process.env.PGDATABASE ?? 'testdb',\n    password: process.env.PGPASSWORD ?? 'password',\n    port: parseInt(process.env.PGPORT ?? '5432', 10),\n  });\n\n  try {\n    await client.connect();\n    console.log('Connected to PostgreSQL.');\n\n    // Create a temporary table with relevant types\n    await client.query(`\n      CREATE TEMP TABLE IF NOT EXISTS my_types_test (\n        id SERIAL PRIMARY KEY,\n        big_int_col BIGINT,\n        timestamp_tz_col TIMESTAMPTZ\n      );\n    `);\n    console.log('Temporary table created.');\n\n    // Insert data (BIGINT as string, Date object for timestamp)\n    const insertResult = await client.query(\n      'INSERT INTO my_types_test(big_int_col, timestamp_tz_col) VALUES ($1, $2) RETURNING *',\n      ['12345', new Date()]\n    );\n    console.log('Inserted row:', insertResult.rows[0]);\n\n    // Query data to see custom parsing in action\n    const selectResult = await client.query('SELECT * FROM my_types_test');\n    const parsedRow = selectResult.rows[0];\n\n    console.log('\\nQueried row with custom parsers:');\n    console.log(`- big_int_col (parsed as Number): ${parsedRow.big_int_col} (Type: ${typeof parsedRow.big_int_col})`);\n    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})`);\n\n  } catch (err) {\n    console.error('Error during example execution:', err);\n  } finally {\n    await client.end();\n    console.log('Client disconnected.');\n  }\n}\n\nrunPgTypeParsersExample();\n","lang":"typescript","description":"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."},"warnings":[{"fix":"Only 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.","message":"Converting 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.","severity":"breaking","affected_versions":">=1.0.0"},{"fix":"Always 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.","message":"PostgreSQL'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.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Refer 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.","message":"`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.","severity":"gotcha","affected_versions":"*"},{"fix":"Monitor `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.","message":"Upcoming `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.","severity":"breaking","affected_versions":">=5.0.0 (anticipated)"}],"env_vars":null,"last_verified":"2026-04-19T00:00:00.000Z","next_check":"2026-07-18T00:00:00.000Z","problems":[{"fix":"Convert environment variables to numbers using `parseInt()` or `Number()`: `port: parseInt(process.env.PGPORT ?? '5432', 10),`. Always provide a fallback default value.","cause":"Environment variables (e.g., `process.env.PGPORT`) are always strings or undefined. TypeScript requires explicit conversion for numeric configuration properties.","error":"Type 'string | undefined' is not assignable to type 'number | undefined'."},{"fix":"If 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.","cause":"`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.","error":"My `COUNT(*)` or `BIGINT` columns are returned as strings, not numbers."},{"fix":"Identify 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.","cause":"`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.","error":"Custom array types or ENUMs are returned as raw strings (e.g., '{value1,value2}')."}],"ecosystem":"npm"}