Can't match type unknown to int in update from values query

[phiresky]

This is caused by postgresql not knowing the type of values() with parameterized values.

example:

const updateTmpTable = values(fromItemTypes(asset_classes, ["market_hash_name",  "can_deposit", "coin_price"]), updateValues).as("temp");

const updateQuery = update(asset_classes).from(updateTmpTable)
    .where({ market_hash_name: asset_classes.market_hash_name })
    .set({
        coin_price: updateTmpTable.coin_price.cast(tInteger),
        can_deposit: updateTmpTable.can_deposit.cast(tBoolean),
        updated: new Date()
    });

The casts in .set({ should not be needed but they are.

The problem is that for inline tables like

(values (100190, true), (100125, true)) as foo(id, exists)

you can not specify the types of the columns as far as I know.

The same is needed for the where query, otherwise I'm getting operator does not exist: text = bigint for a simple bigint primary key comparison because it apparently interprets the other value as text.

[phiresky]

It might be needed to create a temporary type. Example:

instead of

select * from (values (100190, true), (100125, true)) as foo(id, exists)

do this

create type temp as (id int, exists boolean);
select * from (values (23, true), (56, true)) as test;

[phiresky]

another example:

query

const existingMeals = await db.exec(
            sql.from(chkTable)
                .innerJoin(menu.meal)
                .on({ _mensa: chkTable._mensa,
                    _line: chkTable._line,
                    _date: chkTable._date.cast(tDate), // TODOHEDIET
                    _mealIndex: chkTable._mealindex//.cast(sql.tInteger)
                })
                .select(menu.meal.$all)

generated sql:

  sql SELECT "menu"."meal".* FROM (VALUES ($1, $2, $3, $4), ($5, $6, $7, $8))
AS "temp"(_mensa, _line, _date, _mealindex) 
JOIN "menu"."meal"
ON "menu"."meal"."_mensa" = "temp"."_mensa"
AND "menu"."meal"."_line" = "temp"."_line"
AND "menu"."meal"."_date" = "temp"."_date"::date
AND "menu"."meal"."_mealIndex" = "temp"."_mealindex"

 [ 'adenauerring',
  'l1',
  2014-12-10T23:00:00.000Z,
  0,
  'adenauerring',
  'l1',
  2014-12-10T23:00:00.000Z,
  1 ] 

error:

  name: 'error',
  length: 204,
  severity: 'ERROR',
  code: '42883',
  detail: undefined,
  hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
  position: '15780',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_oper.c',
  line: '726',
  routine: 'op_error' }

[phiresky]

An interesting alternative for inserting / updating an unbounded amount of values in general is

await pool.query("insert into test (param1, param2) values (unnest($1::int[]), unnest($2::text[]))", [[1, 2], ["foo", "bar"]]);

General variant:

select * from unnest('{1,2,3}'::int[], '{a,b,c}'::text[]) as x(a,b);

Which results in a much better prepared query. Might not be applicable in all cases though.