使用pg-promise实现PostgreSQL带外键子查询的插入操作

嘿，我帮你把这个PostgreSQL的外键子查询插入逻辑用pg-promise实现出来，咱们分几种清晰的方式来做，还会加一些实用的注意点～

先确认你的表结构（和你给出的一致）：

CREATE TABLE company (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, companyCode CHAR(4) NOT NULL UNIQUE);
CREATE TABLE customer (id SERIAL PRIMARY KEY, company NOT NULL REFERENCES company (id), name VARCHAR(100) NOT NULL);

接下来是pg-promise的实现步骤：

第一步：初始化pg-promise连接 #

先确保你已经安装了pg-promise，然后初始化数据库连接：

const pgp = require('pg-promise')();
// 替换成你的数据库连接信息
const db = pgp('postgres://your_username:your_password@your_host:your_port/your_database');

方式1：贴近原始SQL的参数化查询 #

这种写法最接近你原来的插入语句，同时用参数化查询避免SQL注入：

async function addCustomer(customerName, companyCode) {
  // 编写带参数的插入SQL，$1对应customerName，$2对应companyCode
  const insertQuery = `
    INSERT INTO customer (name, company)
    VALUES ($1, (SELECT id FROM company WHERE companyCode = $2))
    RETURNING id; -- 可选：返回插入的客户ID，方便后续操作
  `;
  
  // 使用db.one执行查询，确保返回一条结果
  const result = await db.one(insertQuery, [customerName, companyCode]);
  return result.id;
}

// 调用示例
addCustomer('Bill Gates', 'MSFT')
  .then(customerId => console.log(`成功新增客户，ID：${customerId}`))
  .catch(err => console.error('插入失败：', err));

方式2：用pg-promise查询构建器优化写法 #

如果想让代码更具可维护性，可以用pg-promise的查询构建器来拆分逻辑：

const { query: q } = pgp;

async function addCustomer(customerName, companyCode) {
  // 先定义子查询：根据companyCode获取company id
  const getCompanyId = q(`SELECT id FROM company WHERE companyCode = $1`, [companyCode]);
  // 再定义主插入查询
  const insertCustomer = q(`
    INSERT INTO customer (name, company)
    VALUES ($1, $2)
    RETURNING id
  `, [customerName, getCompanyId]);
  
  const result = await db.one(insertCustomer);
  return result.id;
}

重要注意事项 #

• 处理不存在的companyCode：如果传入的companyCode在company表中找不到，子查询会返回null，而customer表的company字段是NOT NULL约束，这时候会直接抛出错误。你可以提前检查公司是否存在，或者在SQL里主动触发明确的错误：

  INSERT INTO customer (name, company)
  VALUES ($1, COALESCE((SELECT id FROM company WHERE companyCode = $2), (SELECT 0/0)))
  RETURNING id;
  

• 始终用参数化查询：绝对不要直接拼接字符串到SQL里，参数化查询不仅能防止SQL注入，还能让pg-promise自动处理数据类型转换。

内容的提问来源于stack exchange，提问作者George Gull