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 )
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 :
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.
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