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?

Can you show the code where you set the parameters for the query? It seems it's an issue with the data types you're using as parameters. — Victor P
– Victor P, Commented Sep 2, 2019 at 0:47
@VictorP The code itself is around 100 lines. It involves making api calls to ebay's server, then parsing the response. The orders data I posted was the result of a console.log of the parameters right before executing the query, so it should be accurate. — Lord Elrond
– Lord Elrond, Commented Sep 2, 2019 at 0:56
I'm not sure if you copy/pasted the exact sql generated or orders array because what you posted here looks fine. Maybe you're missing a comma somewhere and it's shifting the parameters? — Victor P
– Victor P, Commented Sep 2, 2019 at 1:38
@VictorP I figured it out. Working on posting an answer but I'm not sure I understand it exactly. I have to use typecasting for columns after a UNION select statement. — Lord Elrond
– Lord Elrond, Commented Sep 2, 2019 at 1:48

Lord Elrond · Accepted Answer · 2019-09-02 02:25:08Z

14

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.

answered Sep 2, 2019 at 2:25

Lord Elrond

16.5k8 gold badges55 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Victor P Over a year ago

Interesting, I didn't know about this caveat. Seems like a strange behavior, though.Glad you found a solution!

Mike Finch Over a year ago

Casting the $n parameter worked for me also, in a merge statement. My $1 param is a string, but my $2 param is an integer. The following worked. merge into user as tgt using ( values ( $1, $2::integer ) ) as src ( username, age ) ...

Charles Zhao · Accepted Answer · 2025-06-11 04:21:29Z

0

I had a similar issue but my DB column definition is int2, so I had to use ::smallint to cast the type.

edited Jun 11 at 4:21

answered Jun 11 at 4:10

Charles Zhao

18612 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related