From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | Jeremy Smith <jeremy(at)musicsmith(dot)net> |
Cc: | Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Tools for moving normalized data around |
Date: | 2023-01-18 20:19:12 |
Message-ID: | CAFCRh-9McDia8GmccF7EfvCigoqfEY1pkP3DbABJ4NMt=jm1uw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jan 18, 2023 at 9:03 PM Jeremy Smith <jeremy(at)musicsmith(dot)net> wrote:
>> How to do this with two tables connected via one foreign key, that is
>> explained a dozen times in Stackoverflow. But what if the tree is 50
>> tables and 120 foreign key columns?
>> It can be done. But probably not manually.
>>
>> So, as this seems a very usual use-case for normalized data, is there
>> any tooling available? Anywhere? (I searched, I didn't find.)
>
> I haven't used it, but this seems like the problem that Jailer is trying to solve: https://github.com/Wisser/Jailer
Seems to dothe subsetting alright, but it doesn't mention the
conflict-resolution for inserting into an already populated target
schema.
We've done something similar, merging many same-schema DBs into a
single local SQLite DB (with conflicts, thus no constraints),
iteratively resolving the conflicts (SK/PK and NK) per-table in
topological order, propagating PK changes to FKs in child tables
later.
Then load the result into a full-constrained PostgreSQL DB. Clever'er
minds can probably do it all in SQL, but we did it with a combination
of imperative code and SQL. Not exactly your use case Peter, but close
enough I think. I don't think a tool could have done what we did,
it's too ad-hoc and specific to our use case. Took a while, and
required lots of testing (unit tests, and functional QA tests). FWIW.
--DD
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2023-01-18 20:45:00 | Re: Tools for moving normalized data around |
Previous Message | Gavan Schneider | 2023-01-18 20:15:23 | Re: Tools for moving normalized data around |