Writing to PostgreSQL with auto-increment column

Hi all…

Having trouble getting a basic auto-increment column written to my database (PostgreSQL). I’m using the “DB Create Node” to create a column for the primary key:


However, when I run the downstream “DB Writer” node (which does not include the primary key column), I get an SQL error:

I’m trying to view the error log through the View->KNIME Log, but I get an “Unhandled event loop exception” in the “org.eclipse.ui” plug-in.

Thoughts on how to troubleshoot? Or a better way of having KNIME auto-generate primary keys for me? I’m basically just stitching together a few CSV files and want to write to a table with a primary key that KNIME generates.

Jeff

Hi @judwig79 , usually database error messages would appear in the console window rather than the error log. Is there anything in there? I would imagine the error log messages are simply a side-effect of the fact the sql error occurred rather than indicating the cause.

I would consider adding try - catch nodes either side of the DB writer as you should then get the error message written to a flow variable port on the catch node.

Do you know for sure that it is the auto increment column causing the problem, ie does it work if you don’t set it as auto increment, and allow it to be null as a test?

e.g. this screenshot is a mockup I have using an H2 database, and generating an error by making a column too small:

In the Console pane, the database error should be shown like it is with my error, below:

2 Likes

Thanks for the reply. The console window is empty. I tried the try/catch block, the try node wired up properly, I couldn’t attach the “Catch Errors (Var Ports)” to the WB Writer note and I’m not sure how you attached it to the “Column Resorter” node either. Is there something I need to do to get the red port at the top of each?

I dropped the serial column and simply tried creating the data structure with DB Table Creator and using DB Writer to write to an existing table. Here’s the comparison of the column resorter output to the table I’m creating:

Everything looks to line up to me…?

Jeff

Hi @judwig79, you can display the red ports (flow variable ports) by right clicking the node and then choose the “show flow variable ports” option.

It is actually possible to link them without showing the ports if you drag the mouse from “just the right place” on the top right corner of a node, but it takes practice at first.

OK, thanks. I did as you suggested and could capture the error output. I had a mismatch in the spelling of one column name – coordinates. It’s a shame it takes this much effort to get the stack trace!

Appreciate the help!
Jeff

Hi @judwig79 , glad to hear you got to the bottom of the error but I’m surprised it didn’t show up in the console panel.

If I remove the try-catch nodes and execute my failing DB Writer, the full error still appears in my console. I don’t generally have to add the try/catch to find out what the error is, so that does seem stange.
e.g.

1 Like

Thanks. Do you think it maybe has something to do with the error messages I see with KNIME? On start-up, I get the following:

When I try to “Open KNIME Log” I get the “Unhandled event loop exception” once again.

Running the code with error in the column name, I get the following in the “Error Log”:

Jeff