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

Question

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?

Answer 1

To fix this error, you need to cast the types of each column in the select statement:

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

The closet thing I could find to an explanation to this is in @DenisdeBernardy 's comment in this question:

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.