• features
• 数据类型

PostgreSQL 拥有丰富的支持 数据类型 的系统。node-postgres 尽最大努力支持最常见的开箱即用数据类型，并提供可扩展的类型解析器以允许自定义类型序列化和解析。

¥PostgreSQL has a rich system of supported data types. node-postgres does its best to support the most common data types out of the box and supplies an extensible type parser to allow for custom type serialization and parsing.

默认字符串

¥strings by default

如果 node-postgres 没有针对数据库类型的注册类型解析器，它将把数据库类型转换为 JavaScript 字符串。此外，你可以将任何类型作为字符串发送到 PostgreSQL 服务器，node-postgres 将传递它而不以任何方式修改它。要完全绕过类型解析，请执行以下操作。

¥node-postgres will convert a database type to a JavaScript string if it doesn't have a registered type parser for the database type. Furthermore, you can send any type to the PostgreSQL server as a string and node-postgres will pass it through without modifying it in any way. To circumvent the type parsing completely do something like the following.

const queryText = 'SELECT int_col::text, date_col::text, json_col::text FROM my_table'
const result = await client.query(queryText)
 
console.log(result.rows[0]) // will contain the unparsed string value of each column

类型解析示例

¥type parsing examples

uuid + json / jsonb

JavaScript 中没有 uuid/guid 的数据类型，因此 node-postgres 将 uuid 转换为字符串。JavaScript 对 JSON 有很好的支持，node-postgres 通过 JSON.parse 将 json/jsonb 对象直接转换为其 JavaScript 对象。同样，通过来自 node-postgres 的查询将对象发送到 PostgreSQL 服务器，node-postgres 将在你的出站值上调用 JSON.stringify，自动将其转换为服务器的 json。

¥There is no data type in JavaScript for a uuid/guid so node-postgres converts a uuid to a string. JavaScript has great support for JSON and node-postgres converts json/jsonb objects directly into their JavaScript object via JSON.parse. Likewise sending an object to the PostgreSQL server via a query from node-postgres, node-postgres will call JSON.stringify on your outbound value, automatically converting it to json for the server.

const createTableText = `
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
 
CREATE TEMP TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data JSONB
);
`
// create our temp table
await client.query(createTableText)
 
const newUser = { email: 'brian.m.carlson@gmail.com' }
// create a new user
await client.query('INSERT INTO users(data) VALUES($1)', [newUser])
 
const { rows } = await client.query('SELECT * FROM users')
 
console.log(rows)
/*
output:
[{
  id: 'd70195fd-608e-42dc-b0f5-eee975a621e9',
  data: { email: 'brian.m.carlson@gmail.com' }
}]
*/

date / timestamp / timestamptz

node-postgres 会将 JavaScript 日期对象的实例转换为 PostgreSQL 服务器的预期输入值。同样，当将 date、timestamp 或 timestamptz 列值读回 JavaScript 时，node-postgres 会将该值解析为 JavaScript Date 对象的实例。

¥node-postgres will convert instances of JavaScript date objects into the expected input value for your PostgreSQL server. Likewise, when reading a date, timestamp, or timestamptz column value back into JavaScript, node-postgres will parse the value into an instance of a JavaScript Date object.

const createTableText = `
CREATE TEMP TABLE dates(
  date_col DATE,
  timestamp_col TIMESTAMP,
  timestamptz_col TIMESTAMPTZ
);
`
// create our temp table
await client.query(createTableText)
 
// insert the current time into it
const now = new Date()
const insertText = 'INSERT INTO dates(date_col, timestamp_col, timestamptz_col) VALUES ($1, $2, $3)'
await client.query(insertText, [now, now, now])
 
// read the row back out
const result = await client.query('SELECT * FROM dates')
 
console.log(result.rows)
// {
// date_col: 2017-05-29T05:00:00.000Z,
// timestamp_col: 2017-05-29T23:18:13.263Z,
// timestamptz_col: 2017-05-29T23:18:13.263Z
// }

psql 输出：

¥psql output:

bmc=# select * from dates;
  date_col  |      timestamp_col      |      timestamptz_col
------------+-------------------------+----------------------------
 2017-05-29 | 2017-05-29 18:18:13.263 | 2017-05-29 18:18:13.263-05
(1 row)

node-postgres 将 DATE 和 TIMESTAMP 列转换为在 process.env.TZ 设置的节点进程的本地时间。

¥node-postgres converts DATE and TIMESTAMP columns into the local time of the node process set at process.env.TZ.

注意：我通常在存储日期时使用 TIMESTAMPTZ；否则，从一个时区的进程插入时间并在另一个时区的进程中读出它可能会导致时间出现意外差异。

¥note: I generally use TIMESTAMPTZ when storing dates; otherwise, inserting a time from a process in one timezone and reading it out in a process in another timezone can cause unexpected differences in the time.