sql - PostgreSQL 错误 : column "qty" is of type integer but expression is of type text

Question

下面的查询是根据插入的行数动态生成的。对于每一行数据，还有一个 UNION SELECT陈述。

INSERT INTO account_sale
(qty, price, product_id)

SELECT $1, $2, t.id
  FROM inventory_product AS t
  WHERE  t.ebay_sku = $3

UNION

SELECT $4, $5, t.id
  FROM inventory_product AS t
  WHERE  t.ebay_sku = $6  
...

当我尝试运行查询时，我得到以下信息:

error: column "qty" is of type integer but expression is of type text

此查询的 node.js 页面:

module.exports = async function(orders) {
  const pool = require('./config.js');
  const client = await pool.connect();

  const sql = ...
  const data = [
    1, 1.50, 10, 
    2, 4.50, 11
  ];

  client.query(sql, data).then(res => {
  ...

  }).catch(err => console.log(err));
}

如果我删除 UNION从查询中，像这样:

INSERT INTO account_sale
(qty, price, product_id)

SELECT $1, $2, t.id
  FROM inventory_product AS t
  WHERE  t.ebay_sku = $3

并从 data 中删除第二项，那里 不是 任何错误。

我在这里缺少什么？

Answer 1

要修复此错误，您需要在 select 语句中强制转换每一列的类型:

INSERT INTO account_sale
(qty, price, product_id)

SELECT $1::integer, $2::float, t.id
  FROM inventory_product AS t
  WHERE  t.ebay_sku = $3

UNION

SELECT $4::integer, $5::float, t.id
  FROM inventory_product AS t
  WHERE  t.ebay_sku = $6  
...

我能找到的解释这一点的壁橱里的东西是@DenisdeBernardy 在 this 中的评论。题:

It's due to the way Postgres coerces types. With a single select, it'll infer the types based on the insert part of the statement, whereas with a union, it'll infer the type based on the first line of the union and fallback to text from lack of hints.