How to define a DATE column as primary key in a time-series data?

As per @mlauber’s reply, I’d suggest perhaps using the tablename in the pk name.

by creating a new pkname flow variable as shown above.

join("pk_",$${Stable_name}$$)

Leaving the config as it is, (ie) leaving the “key1” name in the configuration, but putting this in the pkname variable in the keyname should replace it with a unique key per table.

e.g. pk_WRESBAL

1 Like

Thanks. I was able to resolve some workflows, but I’m having problems with others. Will this trick cause issues for just one table being defined in the Table Creator node?

image

Hi @dataHoarder , the error down has nothing to do with the technique being used for creation of the primary key but is due to the data.

According to that message, you have more than one row with the same date.

A primary key is a unique identifier so if you have more than one row with the same value, as the error suggests, then either the data is corrupt or your assumption about being able to use it as a primary key is incorrect.

If data is incorrect, you could take steps to fix it, such as with a duplicate row filter, unique on “date” and decide the basis on which to keep the route (e.g first row or last row encountered, or highest/lowest “value”) prior to the DB Writer.

2 Likes

Thanks @takbb ! It worked for one workflow, but another workflow with duplicate rows displayed this message despite removing the duplicate row:
image

@takbb @mlauber71 any help with the last error message? Appreciate the help earlier.

@dataHoarder this error sounds like there is a relation in your database that would possibly require a field that is not there. You might want to check these restrictions and relations and see if the data you are trying to load would match that. This mostly will be a question of your task /business case I think.

Hi @dataHoarder , the last couple of error messages you have posted are not related to the issue of creating a primary key column (the topic of this thread), which I think has been resolved.

Happy to help with deciphering error messages, but these are now related to your data and database, and I think if you have further errors, they should be asked as new questions so that this thread can be marked as solved, and you will also more likely get assistance from a wider audience.

Without seeing your actual data, and the database tables you have so far created, it’s going to be difficult to identify the specifics of the error messages, especially as these are PostgresSQL messages (of which I am no expert) rather than KNIME messages.

A “relation” in relational database theory terminology is actually what we refer to in relational database implementations as a “table”, rather than a “relationship” which is a “foreign key”. I’m assuming that Postgres is using the aforementioned academic/theory terminology which can be a little confusing.

Translated, I believe this error is saying that your “crypto”.“BTC” table does not contain a “price” column. I cannot immediately understand from your flow how or why a column would not be present, if the table is being built dynamically from the data table.

Can you confirm if the “price” column exists?

3 Likes

You are correct @takbb. What happened is that the BD Table Creator Node that I copied from the prior workflow had the column names saved in it:

I had to click on the Use dynamic settings to fetch the column names for the current workflow and it worked.

Thanks @mlauber71 as well.

3 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.