0
# Type System
1

2
The pg type system provides comprehensive data conversion between JavaScript and PostgreSQL types, with support for custom type parsers, extensible type handling, and both text and binary format support.
3

4
## Capabilities
5

6
### Type Parser Management
7

8
The main types object provides global type parser configuration.
9

10
```javascript { .api }
11
/**
12
 * Global type parser registry from pg-types module
13
 * Handles conversion between PostgreSQL and JavaScript types
14
 */
15
interface types {
16
  /**
17
   * Get the parser function for a PostgreSQL type
18
   * @param oid - PostgreSQL type OID
19
   * @param format - Format ('text' or 'binary', default 'text')
20
   * @returns Parser function that converts string to JavaScript type
21
   */
22
  getTypeParser(oid: number, format?: string): (value: string) => any;
23
  
24
  /**
25
   * Set a custom parser for a PostgreSQL type
26
   * @param oid - PostgreSQL type OID
27
   * @param format - Format ('text' or 'binary')
28
   * @param parseFn - Parser function
29
   */
30
  setTypeParser(oid: number, format: string, parseFn: (value: string) => any): void;
31
  
32
  /**
33
   * Set a custom parser for a PostgreSQL type (defaults to text format)
34
   * @param oid - PostgreSQL type OID
35
   * @param parseFn - Parser function
36
   */
37
  setTypeParser(oid: number, parseFn: (value: string) => any): void;
38
}
39
```
40

41
**Usage Examples:**
42

43
```javascript
44
const { types } = require('pg');
45

46
// Get existing parser
47
const dateParser = types.getTypeParser(1082, 'text');
48
console.log(dateParser('2023-12-25')); // Parsed date
49

50
// Set custom date parser to return Date objects
51
types.setTypeParser(1082, (val) => new Date(val));
52

53
// Set custom JSON parser with error handling
54
types.setTypeParser(114, (val) => {
55
  try {
56
    return JSON.parse(val);
57
  } catch (err) {
58
    console.warn('Invalid JSON:', val);
59
    return val;
60
  }
61
});
62

63
// Binary format parser
64
types.setTypeParser(17, 'binary', (val) => {
65
  return Buffer.from(val, 'hex');
66
});
67

68
// Numeric type with precision handling
69
types.setTypeParser(1700, (val) => {
70
  return parseFloat(val);
71
});
72
```
73

74
### TypeOverrides Class
75

76
Per-client type parser overrides for customized type handling.
77

78
```javascript { .api }
79
/**
80
 * Client-specific type parser overrides
81
 * Simple container for text and binary type parsers
82
 */
83
function TypeOverrides(userTypes) {
84
  this.text = userTypes || {};
85
  this.binary = userTypes || {};
86
}
87

88
TypeOverrides.prototype.setTypeParser = function(oid, format, parseFn) {
89
  if (typeof format === 'function') {
90
    parseFn = format;
91
    format = 'text';
92
  }
93
  this[format][oid] = parseFn;
94
};
95

96
TypeOverrides.prototype.getTypeParser = function(oid, format) {
97
  format = format || 'text';
98
  return this[format][oid];
99
};
100
```
101

102
**Usage Examples:**
103

104
```javascript
105
const { TypeOverrides } = require('pg');
106

107
// Create client-specific type overrides
108
const customTypes = new TypeOverrides();
109

110
// Override date parsing for this client only
111
customTypes.setTypeParser(1082, (val) => {
112
  const date = new Date(val);
113
  return date.toISOString().split('T')[0]; // Return YYYY-MM-DD format
114
});
115

116
// Use with client
117
const client = new Client({
118
  // ... connection config
119
  types: customTypes
120
});
121

122
// Override JSON parsing with custom reviver
123
const apiTypes = new TypeOverrides();
124
apiTypes.setTypeParser(114, (val) => {
125
  return JSON.parse(val, (key, value) => {
126
    // Convert ISO date strings to Date objects
127
    if (typeof value === 'string' && /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}/.test(value)) {
128
      return new Date(value);
129
    }
130
    return value;
131
  });
132
});
133

134
const apiClient = new Client({
135
  // ... connection config
136
  types: apiTypes
137
});
138
```
139

140
### Common PostgreSQL Type OIDs
141

142
Reference for common PostgreSQL type identifiers (use these numeric values when setting custom parsers).
143

144
```javascript
145
// Numeric types
146
types.setTypeParser(21, parseFn);    // smallint (INT2)
147
types.setTypeParser(23, parseFn);    // integer (INT4)
148
types.setTypeParser(20, parseFn);    // bigint (INT8)
149
types.setTypeParser(700, parseFn);   // real (FLOAT4)
150
types.setTypeParser(701, parseFn);   // double precision (FLOAT8)
151
types.setTypeParser(1700, parseFn);  // numeric/decimal (NUMERIC)
152

153
// String types
154
types.setTypeParser(18, parseFn);    // char (CHAR)
155
types.setTypeParser(1043, parseFn);  // varchar (VARCHAR)
156
types.setTypeParser(25, parseFn);    // text (TEXT)
157
types.setTypeParser(19, parseFn);    // name (NAME)
158

159
// Date/time types
160
types.setTypeParser(1082, parseFn);  // date (DATE)
161
types.setTypeParser(1083, parseFn);  // time (TIME)
162
types.setTypeParser(1114, parseFn);  // timestamp without timezone (TIMESTAMP)
163
types.setTypeParser(1184, parseFn);  // timestamp with timezone (TIMESTAMPTZ)
164
types.setTypeParser(1186, parseFn);  // interval (INTERVAL)
165

166
// Other common types
167
types.setTypeParser(17, parseFn);    // bytea (BYTEA)
168
types.setTypeParser(16, parseFn);    // boolean (BOOL)
169
types.setTypeParser(114, parseFn);   // json (JSON)
170
types.setTypeParser(3802, parseFn);  // jsonb (JSONB)
171
types.setTypeParser(2950, parseFn);  // uuid (UUID)
172

173
// Array types
174
types.setTypeParser(1007, parseFn);  // integer[] (INT4_ARRAY)
175
types.setTypeParser(1009, parseFn);  // text[] (TEXT_ARRAY)
176
```
177

178
**Usage Examples:**
179

180
```javascript
181
// Custom UUID parser
182
types.setTypeParser(2950, (val) => val.toUpperCase());
183

184
// Custom JSONB parser
185
types.setTypeParser(3802, (val) => JSON.parse(val));
186

187
// Custom timestamp parser
188
types.setTypeParser(1184, (val) => new Date(val));
189

190
// Array type parsing
191
types.setTypeParser(1007, (val) => {
192
  // Parse PostgreSQL array format: {1,2,3}
193
  return val.slice(1, -1).split(',').map(Number);
194
});
195

196
types.setTypeParser(1009, (val) => {
197
  // Handle quoted strings in arrays: {"hello","world"}
198
  return val.slice(1, -1).split(',').map(s => s.replace(/^"|"$/g, ''));
199
});
200
```
201

202
### Built-in Type Conversions
203

204
Standard type conversions provided by pg-types.
205

206
```javascript { .api }
207
/**
208
 * Default type conversions applied by pg
209
 * These can be overridden using setTypeParser
210
 */
211
interface DefaultTypeConversions {
212
  // Numeric conversions
213
  smallint: number;        // INT2 -> number
214
  integer: number;         // INT4 -> number  
215
  bigint: string;          // INT8 -> string (to avoid precision loss)
216
  real: number;            // FLOAT4 -> number
217
  double: number;          // FLOAT8 -> number
218
  numeric: string;         // NUMERIC -> string (preserves precision)
219
  
220
  // String conversions
221
  char: string;            // CHAR -> string
222
  varchar: string;         // VARCHAR -> string
223
  text: string;            // TEXT -> string
224
  
225
  // Date/time conversions
226
  date: string;            // DATE -> 'YYYY-MM-DD'
227
  time: string;            // TIME -> 'HH:MM:SS'
228
  timestamp: string;       // TIMESTAMP -> 'YYYY-MM-DD HH:MM:SS'
229
  timestamptz: string;     // TIMESTAMPTZ -> ISO string
230
  interval: string;        // INTERVAL -> PostgreSQL interval format
231
  
232
  // Other conversions
233
  boolean: boolean;        // BOOL -> boolean
234
  bytea: Buffer;           // BYTEA -> Buffer
235
  json: string;            // JSON -> string (not parsed)
236
  jsonb: string;           // JSONB -> string (not parsed)
237
  uuid: string;            // UUID -> string
238
}
239
```
240

241
### Custom Type Parser Examples
242

243
Common patterns for implementing custom type parsers.
244

245
```javascript { .api }
246
/**
247
 * Example custom parsers for common use cases
248
 */
249
```
250

251
**Usage Examples:**
252

253
```javascript
254
// Parse bigint as number (with precision loss warning)
255
types.setTypeParser(20, (val) => {
256
  const num = parseInt(val, 10);
257
  if (num > Number.MAX_SAFE_INTEGER) {
258
    console.warn('Bigint value exceeds JavaScript safe integer range');
259
  }
260
  return num;
261
});
262

263
// Parse numeric/decimal with precision
264
types.setTypeParser(1700, (val) => {
265
  // Use decimal.js for precision
266
  const Decimal = require('decimal.js');
267
  return new Decimal(val);
268
});
269

270
// Parse timestamps as Date objects with timezone handling
271
types.setTypeParser(1114, (val) => {
272
  // Timestamp without timezone - assume UTC
273
  return new Date(val + 'Z');
274
});
275

276
types.setTypeParser(1184, (val) => {
277
  // Timestamp with timezone
278
  return new Date(val);
279
});
280

281
// Parse intervals as duration objects
282
types.setTypeParser(1186, (val) => {
283
  // Parse PostgreSQL interval format
284
  const match = val.match(/(\d+):(\d+):(\d+)/);
285
  if (match) {
286
    return {
287
      hours: parseInt(match[1], 10),
288
      minutes: parseInt(match[2], 10),
289
      seconds: parseInt(match[3], 10)
290
    };
291
  }
292
  return val;
293
});
294

295
// Parse JSON/JSONB automatically
296
types.setTypeParser(114, JSON.parse);   // json
297
types.setTypeParser(3802, JSON.parse);  // jsonb
298

299
// Parse arrays with proper handling
300
types.setTypeParser(1007, (val) => {
301
  // integer[] parser
302
  if (val === '{}') return [];
303
  return val.slice(1, -1).split(',').map(v => v === 'NULL' ? null : parseInt(v, 10));
304
});
305

306
// Parse UUIDs with validation
307
types.setTypeParser(2950, (val) => {
308
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
309
  if (!uuidRegex.test(val)) {
310
    throw new Error(`Invalid UUID format: ${val}`);
311
  }
312
  return val.toLowerCase();
313
});
314

315
// Parse network addresses
316
types.setTypeParser(869, (val) => {  // inet
317
  return {
318
    address: val.split('/')[0],
319
    netmask: val.split('/')[1] ? parseInt(val.split('/')[1], 10) : 32
320
  };
321
});
322
```
323

324
### Type Parser Debugging
325

326
Utilities for debugging and inspecting type parsing.
327

328
```javascript { .api }
329
/**
330
 * Debug utilities for type system
331
 */
332
```
333

334
**Usage Examples:**
335

336
```javascript
337
// Log all registered parsers
338
function debugTypeParsers() {
339
  const { types } = require('pg');
340
  
341
  // Common type OIDs to check
342
  const typeOIDs = [16, 17, 20, 21, 23, 25, 114, 1082, 1114, 1184, 1700, 2950, 3802];
343
  
344
  typeOIDs.forEach(oid => {
345
    const textParser = types.getTypeParser(oid, 'text');
346
    const binaryParser = types.getTypeParser(oid, 'binary');
347
    console.log(`OID ${oid}: text=${textParser.name || 'anonymous'}, binary=${binaryParser.name || 'anonymous'}`);
348
  });
349
}
350

351
// Test type parsing
352
function testTypeParser(oid, testValue) {
353
  const { types } = require('pg');
354
  const parser = types.getTypeParser(oid, 'text');
355
  
356
  try {
357
    const result = parser(testValue);
358
    console.log(`OID ${oid}: "${testValue}" -> ${typeof result} ${JSON.stringify(result)}`);
359
    return result;
360
  } catch (err) {
361
    console.error(`OID ${oid}: Error parsing "${testValue}":`, err.message);
362
    return null;
363
  }
364
}
365

366
// Usage
367
debugTypeParsers();
368
testTypeParser(1082, '2023-12-25');
369
testTypeParser(114, '{"key": "value"}');
370
testTypeParser(20, '9223372036854775807');
371
```
372

373
## Type System Configuration
374

375
```javascript { .api }
376
/**
377
 * Configuration interfaces for type system
378
 */
379
interface TypeSystemConfig {
380
  /** Global types object */
381
  types: types;
382
  /** Client-specific overrides */
383
  TypeOverrides: typeof TypeOverrides;
384
  /** Common type OID constants */
385
  oids: { [typeName: string]: number };
386
}
387

388
/**
389
 * Type parser function signature
390
 */
391
type TypeParser<T = any> = (value: string) => T;
392

393
/**
394
 * Type override map
395
 */
396
interface TypeOverrideMap {
397
  [oid: number]: TypeParser;
398
}
399
```