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!"