PostgreSQL Type Converters

4.1.0 · active · verified Sun Apr 19

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.

Common errors

Warnings

Install

Imports

Quickstart

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();

view raw JSON →