Need to upsert CSV file into Postgres database

  • 152Views
  • Last Post 3 weeks ago
  • Topic Is Solved
0
votes
Lionel Costa posted this 14 August 2019

Hello,

I have a CSV file with one of the column that is a unique ID.

I would like to upsert the records into a postgres database that has the right columns for mapping.

I have followed the procedure to map the ID column usign a target lookup to match the id column in the csv file with the id in the table, and return null if not found (using the option: Set null when no match found)...

Though when I run it, it gives me the following error message:

In order to perform the UPSERT operation, all the primary key columns of the target object 'sandbox.reonomy_import' must be autogenerated.

Though I have only one primary key (the id column) and it is mapped, I dont understand what I should do from here?

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 15 August 2019

Hi Lionel!

 

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.

 

Best regards,

Mariia

0
votes
Lionel Costa posted this 15 August 2019

This is exactly what I have done - as I already explained in the ticket - and it FAILS !!!!

 

See the rror message:

0
votes
Lionel Costa posted this 15 August 2019

Ok, I didn't read everything.

I will try that asap.

0
votes
Lionel Costa posted this 15 August 2019

It is still failing though I have added the generated id column in the database and changed the pk constraint to the new id column...

0
votes
Mariia Zaharova posted this 16 August 2019

 Please specify the package id or name, so that we are able to check its mapping and provide you with a solution.

At the moment, we can see only the package "Insert sandbox.reonomy_import" that is configured with INSERT operation, not UPSERT.

 

Best regards,

Mariia

0
votes
Lionel Costa posted this 5 weeks ago

https://app.skyvia.com/#/packages/77193

0
votes
Mariia Zaharova posted this 5 weeks ago

Hello Lionel!

Thank you for your reply.

 

This package is configured to perform UPSERT, however, lookup mapping for target id column does not have a selected "Set null when no match found" check box. In this case, if lookup returns null, INSERT will not be performed. Please open options for this lookup and select this check box.

 

Also, the same error "In order to perform the UPSERT operation, all the primary key columns of the target object 'sandbox.reonomy_import' must be autogenerated." still occurs. This means, that your primary key column is still not autogenerated. Please send us DDL script of the 'sandbox.reonomy_import' table.

 

Looking forward to your reply.

0
votes
Lionel Costa posted this 5 weeks ago

The "Set null when no match found" did not change the behavior...

Here is the DDL:

CREATE TABLE sandbox.reonomy_import (

reonomy_idvarchar(36) NULL,

[...]

id serial NOT NULL,

CONSTRAINT reonomy_import_pk PRIMARY KEY (id)

 

);

 

 

0
votes
Mariia Zaharova posted this 4 weeks ago

Hello Lionel!

 

The "Set null when no match found" did not change the behavior...

This option is mandatory when Lookup is used for primary key column in UPSERT operation.

 

In your case, the issue is related to the fact that your primary key column is still NOT autogenerated. You can check simply by trying to insert new record in, e.g., PgAdmin without specifying value for id column. 

If you look at our screenshot, after adding new serial data type column and defining it as primary key column, we have got this definition (sequence was automatically created): https://prnt.sc/osvyj1

 

Table:

CREATE TABLE public."TestSync"

(

    id_pk integer NOT NULL DEFAULT nextval('"TestSync_id_pk_seq"'::regclass),

    id integer NOT NULL,

    name character varying COLLATE pg_catalog."default",

    CONSTRAINT id_pk2 PRIMARY KEY (id_pk)

)

 

Sequence:

CREATE SEQUENCE public."TestSync_id_pk_seq";

 

Some useful topics:

https://stackoverflow.com/questions/23578427/changing-primary-key-int-type-to-serial

https://dba.stackexchange.com/questions/78732/change-existing-column-in-pg-to-auto-incremental-primary-key

https://www.postgresql.org/docs/9.1/datatype-numeric.html

 

Thus, please make your primary key column as autogenerated, then select "Set null when no match found" in Lookup options for target id column and try again.

 

Best regards,

Mariia

0
votes
Lionel Costa posted this 4 weeks ago

I created a new table with the following script:

CREATE SEQUENCE sandbox.import_reonomy_id_pk_seq;

 

CREATE TABLE sandbox.import_reonomy (

id integer NOT NULL DEFAULT nextval('import_reonomy_id_pk_seq'::regclass),

 

reonomy_idvarchar(36) NOT NULL,

(...)

CONSTRAINT import_reonomy_pk PRIMARY KEY (id)

);

 

CREATE INDEX import_reonomy_idx ON sandbox.import_reonomy USING btree (reonomy_id);

I have setup the upsert this way:

 

It still fails with the same error message...

 

0
votes
Lionel Costa posted this 4 weeks ago

I even tried with a table that has only 3 columns: The generated Id, the actual id and a "dumb" information column.

It doesnt work!!!!

 

Here are the scripts for info:

CREATE SEQUENCE sandbox.test_import_reonomy_id_pk_seq;

 

CREATE TABLE sandbox.test_import_reonomy (

id integer NOT NULL DEFAULT nextval('test_import_reonomy_id_pk_seq'::regclass),

other_idinteger NULL,

some_infovarchar(25) NULL,

CONSTRAINT test_import_reonomy_pk PRIMARY KEY (id)

);

 

And the data itself:

 

myid someinfo

1 abc

2 abc

3 abc

4 abc

5 abc

6 abc

7 abc

8 abc

9 abc

10 abc

And the mapping:

 

https://app.skyvia.com/#/packages/77874/edit

0
votes
Mariia Zaharova posted this 4 weeks ago

As we have already mentioned, you need to created column with the serial datatype. Only this datatype is treated by Skyvia as identity column.

Thus, please create table like this:

CREATE TABLE public.test_import_reonomy (

id serial NOT NULL,

other_id integer NULL,

some_info varchar(25) NULL,

CONSTRAINT test_import_reonomy_pk PRIMARY KEY (id)

);

 

After this, sequence will be automatically created and definition of the column will look like this (this change is made automatically by PostgtreSQL, but this column essentially stays with the serial datatype):

id integer NOT NULL DEFAULT nextval('test_import_reonomy_id_pk_seq'::regclass)

NOTE: if you create table with the integer datatype (even with sequence), not with the serial one, it will not be treated as identity column.

 

Only explicitly declared serial primary column will be treated by Skyvia as autogenerated (identity) column. In this case, UPSERT works as expected.

See:

0
votes
Lionel Costa posted this 4 weeks ago

I confirm, the serial definition did the trick for the test table.

I am testing the same method for the full table - so far it looks like it is working.

I suggest to change the documentation to update it to the right way of doing upserts...

0
votes
Mariia Zaharova posted this 3 weeks ago

Glad to see that the issue is resolved!

Thank you for your suggestion, we will definitely extend our documentation.

 

Best regards,

Mariia

Close