Data cleaning on 2.c.market.js

[daissatou2]

Describe the bug

We see the error: "invalid input syntax for integer: NaN" when NaN is recieved in the message in the field total_surveyed.

Update the job to replace NaN with undefined so the job just ignores this field when there is bad data.

To Reproduce

1. Here is a link to a failed run on OpenFn.org which is indicative of the bug: https://www.openfn.org/projects/p5yaxp/runs/0616ed56-cc8d-72cf-b796-58faa59b7b7b

expression.js

Link to the job itself in Github: https://github.com/OpenFn/wcs-wildmeat/blob/master/jobs/2.c.market.js

state.json

Postgres DB: See LP Wildmeat Postgres Transitional DB (WCS) https://www.openfn.org/projects/p5yaxp/messages/0616eb1e-a66a-742c-92cd-29aae282d2a0

Expected behavior

NaN should not stop the job from running. This field should just be removed from the mapping if it is NaN.

To test/resolve

1. After the desired output is working locally (from the CLI), please [push commits to master || open a pull request].
2. [Please test the change on OpenFn.org by re-running the links above and confirming success.]

[lakhassane]

@daissatou2 I think you linked the wrong run. That run is for offTake

[aleksa-krolls]

@lakhassane see run here: https://www.openfn.org/projects/p5yaxp/runs/0616eb1e-ab17-7b34-adb9-c85ef3074b95

[lakhassane]

No worries I found the right run.

To avoid having this behavior again, I updated the adaptor (language-postgresql) to accept option to setNull whatever value you want.

So the operation that was failing can be rewrited as this:

fn(async state => {
  ...
  return upsert(
    'tbl_sample_market',
    'ON CONSTRAINT tbl_sample_market_pkey',
    {
     ...
    },
    {
      setNull: ["'NaN'", "'undefined'"]
    }
  )(state);
});

In this case 'NaN' and undefined are replaced by NULL when inserting the data (but it only works if the column is not a NOT NULL column :) PS: language-mssql already had this option cc: @aleksa-krolls

[lakhassane]

Job updated as well: https://www.openfn.org/projects/p5yaxp/runs/06171641-c084-7834-bc80-977fbec7bec9

Feel free to remove the logValues at line 48 if not needed