topic Re: Help! Postgres - Bad value for type int in Talend Studio

Help! Postgres - Bad value for type int

Anonymous — Sat, 16 Nov 2024 12:33:21 GMT

I'm new to TOS, and have gotten about 10 simple jobs working, but I'm getting the below error on 2 jobs and have no idea how to resolve it.
Exception in component tPostgresqlInput_1
org.postgresql.util.PSQLException: Bad value for type int :
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2699)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2016)
at test1_lucid2infinidb.accounting_cart_0_1.Accounting_cart.tPostgresqlInput_1Process(Accounting_cart.java:1019)
at test1_lucid2infinidb.accounting_cart_0_1.Accounting_cart.runJobInTOS(Accounting_cart.java:1681)
at test1_lucid2infinidb.accounting_cart_0_1.Accounting_cart.main(Accounting_cart.java:1549)
There are integer columns that have some null values in the input table (what I thought was originally causing the error), so I tried wrapping a COALESCE(column_name, 0) around the suspected offending columns in the schema query that is issued (which would return the column value or 0 if the column is null), however I still get the same error. I've also tried setting all the integer columns in the schema to string inside of TOS, still no dice. I've searched this forum and found a few similar posts, but I've already tried everything that I've seen suggested.
Here's what the table looks like:
Table "accounting.cart"
Column | Type | Modifiers
--------------------------+--------------------------+-----------
cart_id | integer | not null
account_id | integer | not null
payment_date | timestamp with time zone | not null
currency_code | character varying | not null
exchange_rate | numeric |
state_code | character varying | not null
state_name | character varying | not null
country_id | integer |
country_code | character varying | not null
zip_code | character varying | not null
payment_type | character varying | not null
parent_id | integer |
invoice_status | character varying |
invoice_department | character varying |
invoice_reason | character varying |
distribution_channel | character varying |
ingram_note | character varying | not null
revenue_recognition_date | timestamp with time zone |
buyer_channel_id | integer | not null
is_sale_cart | boolean | not null
checkout_date | timestamp with time zone |
sale_date | timestamp with time zone |
ship_method | character varying |
cart_status_id | integer | not null

Can anyone help?
Best regards,
Dave Sisk

Re: Help! Postgres - Bad value for type int

Anonymous — Wed, 23 Nov 2011 02:17:19 GMT

Hi
Could you show me your SQL statements about COALESCE?
I guess, indeed, you didn't correct datas in the table. It still has null values.
Best regards!
Pedro

Re: Help! Postgres - Bad value for type int

Anonymous — Mon, 28 Nov 2011 15:30:52 GMT

I don't think I still have that SQL statement handy, but it looked like "SELECT .....coalesce(country_id, 0)....FROM cart".
In the metadata definition, the nullable integer columns show as nullable, so TOS knows it could get a null value...why isn't TOS able to handle this already?
Cheers,
Dave

Re: Help! Postgres - Bad value for type int

Anonymous — Mon, 28 Nov 2011 16:00:59 GMT

OK, I re-constructed the SQL statement, here it is...I've put a coalesce(column, -1) around every nullable number column. I still get the error. What am I missing???
SELECT
"lucid"."accounting"."cart"."cart_id",
"lucid"."accounting"."cart"."account_id",
"lucid"."accounting"."cart"."payment_date",
"lucid"."accounting"."cart"."currency_code",
coalesce("lucid"."accounting"."cart"."exchange_rate", -1),
"lucid"."accounting"."cart"."state_code",
"lucid"."accounting"."cart"."state_name",
coalesce( "lucid"."accounting"."cart"."country_id",-1),
"lucid"."accounting"."cart"."country_code",
"lucid"."accounting"."cart"."zip_code",
"lucid"."accounting"."cart"."payment_type",
coalesce("lucid"."accounting"."cart"."parent_id",-1),
"lucid"."accounting"."cart"."invoice_status",
"lucid"."accounting"."cart"."invoice_department",
"lucid"."accounting"."cart"."invoice_reason",
"lucid"."accounting"."cart"."distribution_channel",
"lucid"."accounting"."cart"."ingram_note",
"lucid"."accounting"."cart"."revenue_recognition_date",
"lucid"."accounting"."cart"."buyer_channel_id",
"lucid"."accounting"."cart"."is_sale_cart",
"lucid"."accounting"."cart"."checkout_date",
"lucid"."accounting"."cart"."sale_date",
"lucid"."accounting"."cart"."ship_method",
"lucid"."accounting"."cart"."cart_status_id"
FROM "lucid"."accounting"."cart"

Re: Help! Postgres - Bad value for type int

Anonymous — Mon, 28 Nov 2011 16:45:56 GMT

Hi...Try putting the COALESCE() around all the int fields including "cart_status_id". Maybe the null check fires after the type assignment?

Re: Help! Postgres - Bad value for type int

Anonymous — Mon, 28 Nov 2011 19:15:20 GMT

OK, tried that...still get the same error. Here's the new SQL:
SELECT
coalesce("lucid"."accounting"."cart"."cart_id", -1),
coalesce("lucid"."accounting"."cart"."account_id", -1),
"lucid"."accounting"."cart"."payment_date",
"lucid"."accounting"."cart"."currency_code",
coalesce("lucid"."accounting"."cart"."exchange_rate", -1),
"lucid"."accounting"."cart"."state_code",
"lucid"."accounting"."cart"."state_name",
coalesce( "lucid"."accounting"."cart"."country_id",-1),
"lucid"."accounting"."cart"."country_code",
"lucid"."accounting"."cart"."zip_code",
"lucid"."accounting"."cart"."payment_type",
coalesce("lucid"."accounting"."cart"."parent_id",-1),
"lucid"."accounting"."cart"."invoice_status",
"lucid"."accounting"."cart"."invoice_department",
"lucid"."accounting"."cart"."invoice_reason",
"lucid"."accounting"."cart"."distribution_channel",
"lucid"."accounting"."cart"."ingram_note",
"lucid"."accounting"."cart"."revenue_recognition_date",
coalesce("lucid"."accounting"."cart"."buyer_channel_id", -1),
"lucid"."accounting"."cart"."is_sale_cart",
"lucid"."accounting"."cart"."checkout_date",
"lucid"."accounting"."cart"."sale_date",
"lucid"."accounting"."cart"."ship_method",
coalesce("lucid"."accounting"."cart"."cart_status_id", -1)
FROM "lucid"."accounting"."cart"

Re: Help! Postgres - Bad value for type int

Anonymous — Mon, 28 Nov 2011 20:36:30 GMT

I put a LIMIT 1 on the SQL so I only get one row...even the first row generates this error. Here's the SQL for that, and below the SQL is the values of the one returned row (pulled from pgAdmin with the same query). Same number of values, no numerical nulls...I don't see anything wrong with this data. Does anyone see anything I'm missing???
SELECT
coalesce("lucid"."accounting"."cart"."cart_id", -1),
coalesce("lucid"."accounting"."cart"."account_id", -1),
"lucid"."accounting"."cart"."payment_date",
"lucid"."accounting"."cart"."currency_code",
coalesce("lucid"."accounting"."cart"."exchange_rate", -1),
"lucid"."accounting"."cart"."state_code",
"lucid"."accounting"."cart"."state_name",
coalesce( "lucid"."accounting"."cart"."country_id",-1),
"lucid"."accounting"."cart"."country_code",
"lucid"."accounting"."cart"."zip_code",
"lucid"."accounting"."cart"."payment_type",
coalesce("lucid"."accounting"."cart"."parent_id",-1),
"lucid"."accounting"."cart"."invoice_status",
"lucid"."accounting"."cart"."invoice_department",
"lucid"."accounting"."cart"."invoice_reason",
"lucid"."accounting"."cart"."distribution_channel",
"lucid"."accounting"."cart"."ingram_note",
"lucid"."accounting"."cart"."revenue_recognition_date",
coalesce("lucid"."accounting"."cart"."buyer_channel_id", -1),
"lucid"."accounting"."cart"."is_sale_cart",
"lucid"."accounting"."cart"."checkout_date",
"lucid"."accounting"."cart"."sale_date",
"lucid"."accounting"."cart"."ship_method",
coalesce("lucid"."accounting"."cart"."cart_status_id", -1)
FROM "lucid"."accounting"."cart" limit 1
First row:
4983378;59232872;"2011-01-30 14:17:27-05";"USD";1.0000;"FL";"Florida";840;"USA";"33706";"PAYFLOWPRO";-1;"";"";"";"";"";"2011-02-01 20:06:42-05";0;t;"2011-01-30 14:17:30-05";"2011-01-30 14:17:27-05";"LULU_MAIL";500

Re: Help! Postgres - Bad value for type int

Anonymous — Mon, 28 Nov 2011 21:36:57 GMT

OK, I just create a similar job that reads data from Postgres but outputs the data to a CSV file instead of directly to Infinidb (ie. MySQL with a BI storage engine). I get no input errors from the Postgres driver, and I get a valid CSV output file. Can anyone explain why this would work but outputing to MySQL/Infinidb would cause an input error on the Postgres side?
TIA!
Dave

Re: Help! Postgres - Bad value for type int

Anonymous — Tue, 29 Nov 2011 16:18:46 GMT

Aha! I think I may have found the issue...the source schema changed without my knowledge, and those changes weren't reflected in the target schema. I used a simple Postgres input and MySQL output components...I assume it was automatically mapping columns in a positional manner. So, the error was actually coming from the target schema...which is quite a confusing since the error message certainly *appeared* to be coming from the input schema.
This is worth posting for future folks who get the same confusing error: Check that the source and target schemas match for columns and datatypes.
I've got one of the two jobs running...I'll post if there are any new developments.
Thx,
Dave