时间戳

首页 > 解决方案 > PostgreSQL error: column "qty" is of type integer but expression is of type text

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

问题描述

The query below is dynamically generated based on the number of rows that are being inserted. For each row of data, there is an additional UNION SELECT statement.

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  
...

When I try to run the query, I get the following:

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

The node.js page for this query:

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

If I remove the UNION from the query, like this:

INSERT INTO account_sale
(qty, price, product_id)

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

and remove the second item from data, there isn't any errors.

What am I missing here?

标签： sqlnode.jspostgresqlcasting

解决方案

要修复此错误，您需要在 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 在这个问题的评论中：

这是由于 Postgres 强制类型的方式。使用单个选择，它将根据语句的插入部分推断类型，而对于联合，它将根据并集的第一行推断类型，并从缺少提示中回退到文本。

推荐阅读