Help! Postgres - Bad value for type int

One Star

Help! Postgres - Bad value for type int

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
One Star

Re: Help! Postgres - Bad value for type int

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
One Star

Re: Help! Postgres - Bad value for type int

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
One Star

Re: Help! Postgres - Bad value for type int

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"
One Star

Re: Help! Postgres - Bad value for type int

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

Re: Help! Postgres - Bad value for type int

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"
One Star

Re: Help! Postgres - Bad value for type int

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
One Star

Re: Help! Postgres - Bad value for type int

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
One Star

Re: Help! Postgres - Bad value for type int

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

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch