Handling of PostgreSQL Schema in Knime 2.11 database Nodes

I have some queries about the function of the database table writer nodes and schema when working with PostgreSQL, in particular about defaults.

For example, I created a workflow in Knime 2.10 which used the new database connection nodes to carry out some in-database joins, selections and sorts wrting the results to a series of tables in the database using the 'Database Connection Table Writer'  node. in one of these (for example) I entered the table name in the dialogue as target.tablename (target being the name of the schema). If I omit the schema name then table is written to the public schema. I am keeping everything in lower case btw. The node has a warning associated with it (!) that the existing table will be dropped - which is as I want it. 

This worked OK (although it seems difficult to keep track of schema in Knime e.g. fetch meta data does not identify) but I have just upgraded to Knime 2.11 and now have problems with different behaviour of the above work flow. Now the output has been written to a table in the public schema, i.e. public.target.tablename, i.e. seems to have assume quotes around the name (e.g. CREATE TABLE "target.tablename"). Also, when I repeat the execution for this new table, the table will be dropped warning is still shown but the node fails with message that table already exists..... I note that a specific 'drop table' node has been added in the new version and expect I could use this to sort this but could you confirm that this is the expected behaviour.

I would be grateful also if you could clarify the way Knime is handling schema for PostgreSQL (and other databases).

 

Kind regards

 

 

Steve

 

 

 

 

Hi Steve,

starting with 2.11 we quote the table name in the Database Connection Table Writer when creating the table but unfortunately not when it is dropped. Unfortunately the Drop Table node has the same problem in that it quotes the user input and thus does not support schemas. We will fix these issues with one of the next releases. Sorry about the inconveniences.

Bye,

Tobias

Hi Steve,

the problem with schemas in the Database Connection Table Writer and Database Drop Table node will be fixed with the next release which will be available soon (next week at the latest).

To upgrade your installation open KNIME and go to File -> Update KNIME.

Sorry for any inconveniences this problem has caused you.

Bye,

Tobias

OK, thanks.

Any comment also on options for browsing/viewing table names including schema, e.g. via the fetch metadata option in the database connection ?

regards

Steve

 

Hi Steve,

I have put this on the list for improvements that we will tackle with one of the next KNIME releases.

Thanks for reporting that issue,

Tobias

Hi, me again...

Just wondering how things are going w.r.t. schema handling with Postgresql. Currently we have a Postgresql database with a large number of tables in different schemas and with mutliple partition applied so it is very difficult without significant prior knowledge to find the tables you need by browsing, and in any case the schema are not included so public is assumed .....

Steve

Hi Steve,

the rewrite of the database browser component is still on our list and will be addressed soon. I hope that we will have a new Database Table Selector node including a new database meta data browser with the next release in December but I cannot promise it.

Bye,

Tobias

OK, I just had a discussion with a colleague actually who had the same issue with an Oracle database. At the moment we just need to know the query to access the tables and schema names from the appropriate system tables. Inconvenience rather than a show stopper but will be useful to have. Thanks for the update anyways.

Cheers

 

Steve

Hi all, just a nudge on this post regarding database schema handling - any updates ? R, Steve

Nudge again :slight_smile: ?