[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: managing primary key conflicts while restoring data to table with existing data
From:       Pankaj Jangid <pankaj.jangid () gmail ! com>
Date:       2019-09-27 3:18:03
Message-ID: m2y2yawi90.fsf () gmail ! com
[Download RAW message or body]

Krishnakant Mane <kkmane@riseup.net> writes:

>> You might think about adding the new UUID column and use the existing
>> primary key to inform the updates in dependent tables. Then remove the
>> old PK   column and constraint followed by promoting the UUID to
>> primary key. This could be safely scripted and applied to all
>> instances of your data.  
>> That said, this is only truly necessary of you have production
>> databases to worry about.
>
>
> Thanks a million, this is the most logical and safe way.
>
> yes I have a lot of production databases to worry about.
>
> I am only confused about what you mean by "use the existing primary key
> to inform the updates in dependent tables."
>
> Are you refering to a cascading effect?
>
> If yes then does it mean I first program my upgrade script to manually
> go through all new uuid keys and update the same in the depending tables
> with reference to the old primary key working as foreign key in those
> tables?

I guess that is safest option given that the databases are in
production.

1. add UUID UNIQUI column
2. add references to it by identifying ising pkey
3. remove pkey references and the columns
4. make UUID column the pkey
5. remove old pkey column.

-- 
Pankaj Jangid


[prev in list] [next in list] [prev in thread] [next in thread]