15 August 2019
edited 16 August 2019
15 August 2019
UPSERT operation requires the primary key to be autogenerated.
An UPSERT package uses the primary key in a database to identify an UPSERT. UPSERT works in the following way:
1. If a not null value is provided for the primary key mapping, then the update operation is performed, and Skyvia attempts to update a record with such primary key value.
2. If a null value is provided for primary key mapping, then the insert operation is performed. When the insert operation is performed, the primary key value for this record is unknown, and PostgreSQL must generate it automatically.
For example, in PostgreSQL database primary key column will look like this: http://prntscr.com/osvyj1
This can be achieved by creating new column with serial datatype, removing current primary key constraint and creating new using newly created serial column.
After this, you should configure mapping like this one:
This lookup will perform the following operations: first, it will compare Id from CSV file with Id in PostgreSQL. If such Id is found, the id_pk of this record is returned, and an update is performed.
If there is no such Id in the PostgreSQL Id column, lookup mapping won't find correspondence and will return NULL. As the result, the insert operation is performed and id_pk value will be generated automatically.
For more information, please refer to https://skyvia.com/resources/docs/index.html?performing_upsert_operation.htm
Please tell us if this information helps.