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

10,078

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.

Share:
10,078
Lord Elrond
Author by

Lord Elrond

Updated on June 17, 2022

Comments

  • Lord Elrond
    Lord Elrond over 1 year

    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?