How to write a CASE clause with another column as a condition using knex.js

So my code is like one below:

.select('id','units',knex.raw('case when units > 0 then cost else 0 end'))

but it gives me error like this one

hint: "No operator matches the given name and argument type(s). You might need to add explicit type casts."

Any idea how I should right my code so I can use another column as an condition for different to column ?

Solution

I don't get the same error you do:

CASE types integer and character varying cannot be matched

but regardless, the issue is that you're trying to compare apples and oranges. Postgres is quite strict on column types, so attempting to put an integer 0 and a string (value of cost) in the same column does not result in an implicit cast.

Turning your output into a string does the trick:

  .select(
    "id",
    "units",
    db.raw("CASE WHEN units > 0 THEN cost ELSE '0' END AS cost")
  )

Sample output:

[
  { id: 1, units: null, cost: '0' },
  { id: 2, units: 1.2, cost: '2.99' },
  { id: 3, units: 0.9, cost: '4.50' },
  { id: 4, units: 5, cost: '1.23' },
  { id: 5, units: 0, cost: '0' }
]