I need to define a primary key in a time-series dataset. I have tried this setup with the “DB Table Creator” node (the last end of the workflow shown here):
The settings in the DB Table Creator node:
@dataHoarder have you tried removing the existing table or telling the DB Table Creator to do this?
Yes, same thing. I also tried the Dynamic settings. No avail.
Hi @dataHoarder, can you confirm if it would create the primary key in the db if you use a DB SQL Executor with this statement?
alter table "macro"."WRSEBAL" add primary key ("date")
(I’m assuming the case sensitivity on the schema, table and column names, but otherwise don’t include the double quotes.)
@takbb @dataHoarder indeed this is how it does work:
ALTER TABLE "public"."db_date_as_primary"
ADD PRIMARY KEY("Local_Date");
I have many tables in that macro schema, they all have just two columns named “date” and “value”. Is there a way to apply the SQL code to all tables? And can I do it in the DB Table Creator?
@dataHoarder you can use loops and the DB SQL Exexutor and give the table names via the Flow Variable. It would look like this
@mlauber71 I have rearranged the nodes:
But still, I cannot make the date column a primary key. Please see the result:
@dataHoarder as has been said (1|2) you will have to use the ALTER TABLE command in the DB SQL Executor. You could adapt my example with a loop.
Hi @dataHoarder , the reason for my question was that I was trying to discover if, in your setup, the database was having an inherent problem with creating this specific primary key even if it were actioned explicitly rather than through the DB Table Creator. It’s what I would try in investigating why it’s not working in the DB Table Creator.
And yes it can be done with the DB SQL Executor as part of the processing if that is what it takes, using flow variables as @mlauber71 said. Doing it that way may end up being the more pragmatic solution as it’s often difficult working out such database problems remotely ( without access to the database), unless it’s something that has been observed before.
If you want to give it a try with the DB SQL Executor, add it after the DB Table Creator, at (1) in the following screenshot
and give it the following statement:
alter table "$${SSchemaName}$$"."$${STableName}$$"
add primary key ("date")
keeping all the double quotes in all places if case-sensitive, and replacing the flow variables in my example with your own.
btw, I would generally advise against naming a database column “date” (or using any other reserved word), as although it won’t generally cause problems, it can cause confusion.
Or without using the DB SQL Executor, you could try adding that same statement on the “additional options” tab on the DB Table Creator, which I’d forgotten about… 
That was the first thing I tried but was not able to find the right syntax …
@mlauber71 I am trying to stick with a simple solution that works by default. The same workflow has been suggested for @takbb and seemed to have worked -DB Table Creator creates table without data - #5 by takbb
@takbb if we use DB SQL Executor node, do we even need DB Table Creator node at all? Anyway, that SQL snippet ended up in error 
Overall, I think this should be a rather simple update option in KNIME to set the Primary key.
Hi @dataHoarder , no you don’t need DB Table Creator, and yes it ought to be straightforward but all I can do remotely is ask questions to help troubleshoot.
I haven’t got a postgres db installed at the moment, and I’ve not heard of particular problems creating primary keys on postgres.
What was the error? Happy to continue to (try to) help but need info to do so. If that statement generated errors then does it give some clues?
My bad - you are right. That syntax can’t be used there. I was thinking it allowed an additional statement to be executed at table creation time, but it has to be part of the create table statement in the Additional Options by the look of it.
Actually this is meant to be used when creating tables but in this case it does not seem to work. I do not know why.
Is there any other alternative solution to this issue? I would like to use the Visual tools to set the primary key.
Hi @dataHoarder …
Can you tell us what errors you saw when using the DB SQL Executor? Like I said, it may give clues. It might not, but I have nothing else to go on atm.
Also… which version of KNIME are you using (and which OS), and which version of Postgres, and which driver version?
No error to show for, workflow runs successfully but the column is not becoming primary key.
I’m on Windows latest version, KNIME 5.3.3, and postgres (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1).
The Postgres driver details is below:
I meant when you said earlier “Anyway, that SQL snippet ended up in error”. I was wondering what error came out of the DB SQL Executor, as this may give a clue at what happens with the DB Table Creator.
I downloaded postgres 16 on windows 10 with KNIME 5.3.3 and it creates the primary key from the DB Table Creator as expected, so having trouble working out why it doesn’t for you.
Does it create any constraints at all on that table for you.
Have you changed any of the default config on the PostgresSQL Connector, or set any other config on DB Table Creator? Might be worth uploading screenshots of all your config settings from PostgresSQL Connector and DB Table Creator to see if I can replicate your setup as exactly as I can and see what happens.
