Re: Multiple inserts with two levels of foreign keys

Thanks Peter!

I'll take a close look at your suggestion when I get a chance. But I've
already implemented a Python script that solves my actual problem based on
the pattern that Alvaro Herrera suggested for the toy problem I described
here. It's working very well to reproduce the farm with several levels of
one-to-many dependencies, and should be easy to maintain. I really like
the power of the with clause.

Best,
Dow

On Sun, Oct 8, 2023 at 2:03 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2023-10-05 09:59:24 -0500, Ron wrote:
> > But honestly, the amount of text duplication hurts my "inner
> programmer".
> > And it would have to be generated dynamically, since you don't know how
> many
> > crops were delivered. #shudder
>
> Yes, this seems like the kind of problem that I would definitely solve
> in a script running outside of the database. Especially since it has to
> talk to two databases. If the number of data records isn't too large
> (maybe a few tens of thousands), I'd just write three loops to select
> from the prod database and insert into the dev database.
>
> If the number of records is too large for that, I'd create some staging
> table with an extra column "new_id" filled from the same sequence as the
> original table, like this:
>
> create table new_farms(
> id bigint,
> name character varying(30),
> new_id bigint default nextval('farms_id_seq')
> )
>
> Then you can just COPY the data into these tables and it will give a
> nice mapping from old to new ids which you can use in subsequent
> inserts.
>
> hp
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>