Population of Target Dataset with Autogenerated Primary Key

chiraggiri

I have input dataset in the form of excel file with following format:

WORK_ID

WORK_NAME

RF_NAME

Target Dataset if postgresql table with following def:

CREATE TABLE dl_insight_zone.DIM_INITIATIVE(
INIT_UNIQUE_ID SERIAL PRIMARY KEY,
WORK_ID varchar(80),
WORK_NAME text,
RF_NAME text
);

I want to create auto-generated primary key with each record insertion. However, I am not able to do that. Please suggest options with sync/prepare recipe or any other way to do this.

Zach

Hi @chiraggiri
,

You can accomplish this by using a Window recipe to create an auto-incrementing column, and then setting the column as the primary key:

1. Create a Window recipe with the input set to your Excel dataset and the output set to the Postgres dataset.
2. Configure the Window recipe as follows. The prod_rate_value column can be set to any column (this is the column that the recipe will sort the rows by when adding the auto-incrementing column.
   

3. The output dataset should now have an auto-incrementing column like this:

4. Change the table creation mode of the output dataset in Settings > Advanced. Set the table creation mode to Manually define, and mark the auto-incrementing column as the primary key:

5. Rebuild the dataset. The auto-incrementing column should now be the primary key.

Here's a picture of how it looks in the flow. prod_rate is the Excel dataset, and prod_rate_window is the Postgres dataset:

Feel free to let me know if you have any questions.

Thanks,

Zach