Type mismatches on insert

CloverDX Platform
1

I’ve got a transformation that reads from one table on a Postgres (8.4.4) server, sorts the rows on two columns and then writes to a different table (on a different Postgres 8.4.4 server) with exactly the same structure and column names as the origin table.

Everything runs great until it goes to insert the data into the destination table, and then I get this error:

ERROR: column “total_volume” is of type numeric but expression is of type character varying

I’m using the same metadata for all relevant input and output ports (it was suggested in our phone demo of Clover that this would be OK if the tables had the same structure), and the “total_volume” column is a decimal (length 20, scale 2) field in the metadata. It’s numeric(18,2) in the databases. At any rate, in no location do I see it listed as a varchar.

Is there a setting I need to change to fix this, or something I can check to see what’s really going on?

2

Hello Bob,
the message clearly says, that you are trying to put string value to numeric column. I think that somewhere is missing mapping between clover fields and database fields, so Clover tries to put fields to database in different order, that there have been pulled out. Please check your sql query in DBInputTable - if it contains direct mapping (something like select $field1:=dbField1, $field2:=dbField2 from mytable), you can be sure, which clover fields contain data from which db fields. Check also your DBOutputTable - the best way to ensure proper mapping between clover fields and database fields is to use direct mapping as well (insert into mytable (f1,f2,…,fn) values (val1, $field2, …,$fieldm ).