Re: managing primary key conflicts while restoring data to table with existing data

On 25/09/19 7:50 PM, Adrian Klaver wrote:
> On 9/25/19 12:15 AM, Krishnakant Mane wrote:
>> Hello all,
>>
>> I have been using postgresql for an enterprise quality account's
>> automation and inventory management software called GNUKhata
>> <https://gnukhata.in>
>>
>> Our team is planning to add backup and restore function in the software.
>>
>> But we don't want to dump the entire database and then restore the same.
>>
>> What we are trying to do is to copy data specific to an organization.
>>
>> The challenge here is that I might copy all data (account heads,
>> bills, vouchers etc ) for one organization from an instance on one
>> machine.
>>
>> I take the archive in what ever format to another machine and now
>> attempt to restore.
>>
>> The risk here is for example if the primary key value for orgcode in
>> the organization table is 5, it might conflict with the data where I
>> am attempting it to be restored.
>>
>> Same holds true for bills, invoices etc.
>>
>> A certain account head with accountcode 1 might be already present on
>> the second machine.
>>
>> I am not expecting the users to empty all data from the destination
>> machine before restoring a backup.
>>
>> The reason is that an auditor may have many client's data and one
>> can't predict what primary key values are going to come from a backup.
>>
>> Basically I can even say this is a copy paste instead of a pure
>> backup and restore.
>>
>> Can any one suggest how to handle such conflicts?
>
> Hard to say. If the data is held in common tables(bills, vouchers,
> etc)then the only thing I see happening is changing the PK values to
> an unused value. That could turn into a nightmare though. Not only
> that you lose the connection to the original data source. If the data
> can be broken out into separate tables then I could see placing them
> in their own schema.
>
>>
>>
>> -- 
>> Regards,
>> Krishnakant Mane,
>> Project Founder and Leader,
>> GNUKhata <https://gnukhata.in/>
>> //(Opensource Accounting, Billing and Inventory Management Software)//
>

Hi Adrian,

Even I am thinnking to do some kind of upsert with this situation.

And I would have to set the pkey to an unassigned value when there is
conflict.

I may also choose to revamp the serial by timestamps but don't know if
the target customers would like it.

--
Regards,
Krishnakant Mane,
Project Founder and Leader,
GNUKhata <https://gnukhata.in/>
//(Opensource Accounting, Billing and Inventory Management Software)//