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@hjp.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@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"