Background
Say I have following table that I have processed through KNIME:
| first_name | last_name |
|---|---|
| John | Doe |
| Jane | Doe |
I now want to upload it into my PostgreSQL database, so it becomes:
| id | first_name | last_name |
|---|---|---|
| 1 | John | Doe |
| 2 | Jane | Doe |
I’d like the id column to be filled automatically, and constituted of unique and incrementing values.
I want to append the new data to the existing ones, should there be some already.
On my database, I have thus created the following table:
CREATE TABLE knime_import
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
first_name text NOT NULL,
last_name text NOT NULL,
PRIMARY KEY (id)
)
Problem
I am configuring my Database Writer node with relevant settings, and then run the flow. The console throws me following error:
ERROR Database Writer 2:2 Execute failed: java.lang.Exception: Error while adding row #2 (Row0), reason: ERROR: cannot insert into column "id"
Detail: Column "record_id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
Looking at the logs, I get that:
2019-06-13 18:14:21,788 : DEBUG : main : ExecuteAction : : : Creating execution job for 1 node(s)...
2019-06-13 18:14:21,789 : DEBUG : main : NodeContainer : : : Database Writer 2:2 has new state: CONFIGURED_MARKEDFOREXEC
2019-06-13 18:14:21,789 : DEBUG : main : NodeContainer : : : Database Writer 2:2 has new state: CONFIGURED_QUEUED
2019-06-13 18:14:21,790 : DEBUG : main : NodeContainer : : : knime_import 2 has new state: EXECUTING
2019-06-13 18:14:21,790 : DEBUG : KNIME-Worker-17 : WorkflowManager : Database Writer : 2:2 : Database Writer 2:2 doBeforePreExecution
2019-06-13 18:14:21,790 : DEBUG : KNIME-Worker-17 : NodeContainer : Database Writer : 2:2 : Database Writer 2:2 has new state: PREEXECUTE
2019-06-13 18:14:21,790 : DEBUG : KNIME-Worker-17 : WorkflowManager : Database Writer : 2:2 : Database Writer 2:2 doBeforeExecution
2019-06-13 18:14:21,790 : DEBUG : KNIME-Worker-17 : NodeContainer : Database Writer : 2:2 : Database Writer 2:2 has new state: EXECUTING
2019-06-13 18:14:21,790 : DEBUG : KNIME-Worker-17 : WorkflowDataRepository : Database Writer : 2:2 : Adding handler f4c5fd9f-84af-4ec5-bb62-320025afb0b1 (Database Writer 2:2: <no directory>) - 2 in total
2019-06-13 18:14:21,791 : DEBUG : KNIME-Worker-17 : LocalNodeExecutionJob : Database Writer : 2:2 : Database Writer 2:2 Start execute
2019-06-13 18:14:21,791 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: EXECUTING
2019-06-13 18:14:21,791 : DEBUG : KNIME-Worker-17 : Buffer : Database Writer : 2:2 : Opening input stream on file "/tmp/knime_import_manua69432/knime_container_20190613_460390347208194108.tmp", 1 open streams
2019-06-13 18:14:21,791 : DEBUG : KNIME-Worker-17 : Buffer : Database Writer : 2:2 : Opening input stream on file "/tmp/knime_import_manua69432/knime_container_20190613_6395252698994969926.tmp", 1 open streams
2019-06-13 18:14:21,791 : DEBUG : KNIME-Worker-17 : Buffer : Database Writer : 2:2 : Opening input stream on file "/tmp/knime_import_manua69432/knime_container_20190613_5956790908879331787.tmp", 1 open streams
2019-06-13 18:14:21,791 : DEBUG : KNIME-Worker-17 : Buffer : Database Writer : 2:2 : Opening input stream on file "/tmp/knime_import_manua69432/knime_container_20190613_6451067865448855095.tmp", 1 open streams
2019-06-13 18:14:21,791 : DEBUG : KNIME-Worker-17 : Buffer : Database Writer : 2:2 : Opening input stream on file "/tmp/knime_import_manua69432/knime_container_20190613_8974184074465610225.tmp", 1 open streams
2019-06-13 18:14:21,792 : DEBUG : KNIME-Worker-17 : NodeContext : Database Writer : 2:2 : Workflow user found: ebosi
2019-06-13 18:14:21,792 : DEBUG : KNIME-Worker-17 : RegisteredDriversConnectionFactory : Database Writer : 2:2 : Try to lock key to obtain connection: ConnectionKey [db-user=knime, db-name=jdbc:postgresql://localhost:5432/test_database, wf-user=ebosi]
2019-06-13 18:14:21,792 : DEBUG : KNIME-Worker-17 : RegisteredDriversConnectionFactory : Database Writer : 2:2 : Connection found for key: ConnectionKey [db-user=knime, db-name=jdbc:postgresql://localhost:5432/test_database, wf-user=ebosi]
2019-06-13 18:14:21,808 : DEBUG : KNIME-Worker-17 : RegisteredDriversConnectionFactory : Database Writer : 2:2 : Valid connection found in cache with key: ConnectionKey [db-user=knime, db-name=jdbc:postgresql://localhost:5432/test_database, wf-user=ebosi]
2019-06-13 18:14:21,808 : DEBUG : KNIME-Worker-17 : NodeContext : Database Writer : 2:2 : Workflow user found: ebosi
2019-06-13 18:14:21,808 : DEBUG : KNIME-Worker-17 : DatabaseConnectionSettings : Database Writer : 2:2 : Try to lock key for stmt execution: ConnectionKey [db-user=knime, db-name=jdbc:postgresql://localhost:5432/test_database, wf-user=ebosi]
2019-06-13 18:14:21,808 : DEBUG : KNIME-Worker-17 : DatabaseConnectionSettings : Database Writer : 2:2 : Check connection for key: ConnectionKey [db-user=knime, db-name=jdbc:postgresql://localhost:5432/test_database, wf-user=ebosi]
2019-06-13 18:14:21,824 : DEBUG : KNIME-Worker-17 : PostgreSQLUtility : Database Writer : 2:2 : Checking if table knime_import exists
2019-06-13 18:14:21,824 : DEBUG : KNIME-Worker-17 : PostgreSQLUtility : Database Writer : 2:2 : Execute query: SELECT * FROM (SELECT 1 as tmpcol FROM knime_import) tempTable_2919999700065642121 LIMIT 0
2019-06-13 18:14:21,840 : DEBUG : KNIME-Worker-17 : PostgreSQLUtility : Database Writer : 2:2 : Table knime_import exists
2019-06-13 18:14:21,855 : DEBUG : KNIME-Worker-17 : DBWriterImpl : Database Writer : 2:2 : Executing SQL statement as prepareStatement: INSERT INTO knime_import (firt_name, id, last_name) VALUES (?, ?, ?)
2019-06-13 18:14:21,886 : DEBUG : KNIME-Worker-17 : DBWriterImpl : Database Writer : 2:2 : Rollback complete transaction with auto commit=true
2019-06-13 18:14:21,886 : DEBUG : KNIME-Worker-17 : Node : Database Writer : 2:2 : reset
2019-06-13 18:14:21,886 : ERROR : KNIME-Worker-17 : Node : Database Writer : 2:2 : Execute failed: java.lang.Exception: Error while adding row #2 (Row0), reason: ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : Node : Database Writer : 2:2 : Execute failed: java.lang.Exception: Error while adding row #2 (Row0), reason: ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
java.sql.SQLException: java.lang.Exception: Error while adding row #2 (Row0), reason: ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:691)
at org.knime.core.node.port.database.writer.DBWriterImpl.writeData(DBWriterImpl.java:108)
at org.knime.base.node.io.database.DBWriterNodeModel.execute(DBWriterNodeModel.java:250)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1186)
at org.knime.core.node.Node.execute(Node.java:973)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.lang.Exception: Error while adding row #2 (Row0), reason: ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
at org.knime.core.node.port.database.writer.DBWriterImpl.lambda$0(DBWriterImpl.java:398)
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:684)
... 15 more
Caused by: org.postgresql.util.PSQLException: ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
at org.knime.core.node.port.database.writer.DBWriterImpl.lambda$0(DBWriterImpl.java:375)
... 16 more
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : WorkflowManager : Database Writer : 2:2 : Database Writer 2:2 doBeforePostExecution
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : NodeContainer : Database Writer : 2:2 : Database Writer 2:2 has new state: POSTEXECUTE
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : WorkflowManager : Database Writer : 2:2 : Database Writer 2:2 doAfterExecute - failure
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : Node : Database Writer : 2:2 : reset
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : Node : Database Writer : 2:2 : clean output ports.
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : WorkflowDataRepository : Database Writer : 2:2 : Removing handler f4c5fd9f-84af-4ec5-bb62-320025afb0b1 (Database Writer 2:2: <no directory>) - 1 remaining
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : NodeContainer : Database Writer : 2:2 : Database Writer 2:2 has new state: IDLE
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : Node : Database Writer : 2:2 : Configure succeeded. (Database Writer)
2019-06-13 18:14:21,887 : DEBUG : KNIME-Worker-17 : NodeContainer : Database Writer : 2:2 : Database Writer 2:2 has new state: CONFIGURED
2019-06-13 18:14:21,888 : DEBUG : KNIME-Worker-17 : NodeContainer : Database Writer : 2:2 : knime_import 2 has new state: CONFIGURED
2019-06-13 18:14:21,888 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: IDLE
The interesting line is the following:
2019-06-13 18:14:21,855 : DEBUG : KNIME-Worker-17 : DBWriterImpl : Database Writer : 2:2 : Executing SQL statement as prepareStatement: INSERT INTO knime_import (firt_name, id, last_name) VALUES (?, ?, ?)
Basically, KNIME tries to write on each column… whereas one of them (id), is expected to be filled automatically.
Question
How to prevent Database Writer from trying to write on columns it is not asked to write on — i.e., a GENERATED ALWAYS AS IDENTITY (that is more or less a SERIAL) column?
Config: KNIME Analytics Platform 3.7.2.v201904171038 | postgresql 11
