Database Import in Postgres

Hello,

I would like to import a Knime Table into a postgresql Database via the Database Writer.
The Database Writer creates successfully the Target-Table in my DB, but the Import of the Values fails.
The Knime Table has one Column (String) with Date-Values in the Form ‘2008-12-81’. These Values should be transfered into a Tablefield Type date into the DB.

But I always get the following Error:
WARN DBWriterConnection Error in row #1: 2653536, ERROR: column “gebdatum” is of type date but expression is of type character varying

Any Idea?

Greetings

Frank Theisohn

From the error message it seems that the postgres table was created by hand and the type of the column “gebdatum” was set to Date, but the column “gebdatum” within the incoming KNIME table is of type String. Did you encounter the second tab of the dialog of the Database Writer named “SQL types”?
If you have a column of type Date in your DB and you want to write the data from KNIME into this DB you can set the column type in this dialog tab to Date.
Does that work for you? We tried this here with mySQL and it worked for us.

(However, if you already encountered this tab and set the column type to date you may have to check which syntax Postgres expects to import a date from Strings…)

Let us know if you get it to work. Best,

Fabian

We investigated a bit further and encountered the same problem.
The reason is that as of version 8.xx of the Postgres JDBC driver implementation, setString() for arbitrary column types is no longer possible, instead setInteger(), setDate(), … must be used, as stated in this mailing list.
If an older version of the Postgres JDBC driver is used it works fine. Download here. We tested it with version 7.2 for JDBC2.
Hope that works for you as well. If not let us know.

However, someday we will support Date types within KNIME, which will solve the problem as well.

Hello Again,

first off all sorry für my late response and thanx for your Tips.
Yes, I had changed the SQL-Types in the “Database Writer” Node to Type “date”, but the Error remains.
Changing to the JDBC2 7.2 really worked fine, as long as i use a WIN1252 Encoding in Postgres - knime is now able to write date-Types into the Database.
BUT:
Now there is a problem while writing the ä,ü,ö in varchar. These letters are not correctly imported.

If a use a UTF8 Encoding (which is used in most of my DB’s), I still receive the Error:
WARN DBWriterConnection Error in row #1: 26830600, FEHLER: Spalte »gebdat« hat Typ date, aber der Ausdruck hat Typ character varying

Frank