Postgres - Database writer - relation "a_table" already exists.

KNIME Analytics Platform
1

Hello ,

I’m facing the following issue with the Database Writer.
It’s an issue on Postgres ; the same way of working, on Oracle, DOES work fine.

When using the DB Writer, I do not like the fact that the table is dropped and recreated.
(loss of possible grants, and probably other stuff I don’t think of right now.
I just don’t like the concept of drop/create instead of truncate/insert,
but that’s just me maybe :wink: )

So, I choose the option ‘Append data’, and in my workflow, I add an explicit
“Truncate table” statement in an Databse SQL Executor, before the DB writer.
The truncate works, but the DB Writer returns an error saying
<< relation “my_table” already exists >>.

Hope you can help.

By the way, testing the above has also led me, by coïncidence, to the following 2 issues :

  1. A postgres table with a text column (needed to store varchar longer than 255), that is recreated by the DB writer, is recreated with this column as varchar instead of text.
    So image you created your table explicitely with text columns because you know your data can
    be longer than 255 characters => you’re back to varchar 255 after you ran this flow.

  2. When an Oracle table is transferred into a Postgress table, with the necessary read and write connectors, then the Postgres table gets recreated with all the column names in caps.
    That forces you to write an sql on the Postgres table afterwards with double quotes around the
    column in caps… So imagine you were developing from scratch, extracting an oracle table
    into Postgres. You had created your Postgres table simply with lowercaps columns. After the
    transfer, you have an SQL on the Postgres table, and you just do
    ’ select column1, column2 from my_postgres_table’… You run your statement, and all of sudden
    you get the error : column1 doesn’t exist. After some searching, you find that suddenly it has
    to be " select “COLUMN1” from my_postgres_table ".

So that’s two reasons why I don’t like the DROP/CREATE :wink:

Thx,
Kr,
Piet

2

Hi Piet!

I agree with you that concept of drop/create is not the best and there are flaws in it as you pointed out.

Regarding this error with Append data option checked for empty Postgres table - haven’t experienced it. Can you double check it because this seems awkward and I think I would experience it because I was truncating and writing to Postgres tables a lot :slight_smile: Although not in such way. One difference that pops on my mind is difference in committing in Oracle and Postgres causing this but not sure exactly how.

Regarding issues:

  1. You can specify on on SQL Types tab which type should column be so can avoid this. I guess varchar 255 is a default for String type column in KNIME.

  2. This makes sense as you got caps from Oracle columns. Can avoid it by Extracting column headers, make them lowercase using String Manipulation node and then insert them back as a headers but is a flaw of this concept.

New Database Integration nodes do not have Writer node. Instead they offer Insert, Merge (!) and Update nodes. Check them out :wink:

Br,
Ivan

1 Like
3

Thx !
Indeed Postgres is different from Oracle when it comes to comitting …
(and strange to me, as I’m used to Oracle).
I think the New Dabase Integration nodes will indeed be better for me !

Enjoy your weekend
Kr,
Piet

1 Like
4

You are welcome.

Do hope so :smiley:

Have a nice weekend as well,
Ivan

1 Like