dbxUpsert : what value should I input into the PK dataframe cell of the rows to add?

[asitemade4u]

Hi, and thank you for this excellent package!

I have tested dbx thoroughly but am hitting a snag on dbxUpsert :

1. I create a tibble of records to be added or updated
2. I include in the tibble a column corresponding to the unique primary key in the database (I checked, the Postgres 14 database has a unique, primary key, auto-incrementing column) -- see screenshot from Datagrip below
3. When I execute dbx::Upsert:

• the records already existing update fine
• but the records to be added get the corresponding value in the tibble even if it is already existing as a unique primary key in the table... For example, if I input 1 as a value in the PK cell of the row of the tibble to be inserted, dbxUpsert adds the record to the table without complaining although there is already a record with PK = 1 in the table.

So I tried different things:

PK value	result
0	adds 0 as the PK
-1	adds -1 as the PK
any existing value in the PK	adds it in the PK and does not complain
NA	fails and sends error
NULL	fails and sends error

What am I doing wrong?

[ankane]

Hi @asitemade4u, the database won't let you insert a duplicate value for a primary key (or a unique index), so it doesn't sound like there's one on the table.

[asitemade4u]

Yes, that is precisely what puzzled me so much. And yet, behold, this is the DDL of the table zzz.zzz:

(
    nam     varchar(80),
    cmt     text,
    nbr     integer,
    zzz_key serial
        constraint zzz_pk
            primary key
);

alter table zzz.zzz
    owner to docker;

create unique index zzz_key_uindex
    on zzz.zzz (zzz_key);

grant delete, insert, references, select, trigger, truncate, update on zzz.zzz to postgres;

[ankane]

There shouldn't be a way to insert duplicate values for zzz_key into that schema, so I'd make sure you're not connected to a different database or using a different table.