Hi I often use CSV as a step too: - Dump the data you want to work on, copy/edit or whatever from db 1 in csv - Copy the SQL for the same source table design and use it to create a similar table in db 2 (using a different name where necessary) - import the CSV data into that new table in db2 Then using the usual scripting tools to add/edit/delete the related data in db2.
I was unable to get foreign data wrappers to perform for larger data set updates recently (few hundred million records), at least when those dbs were on remote servers. Transferring a copy via CSV to use for the updates were quick and easy. Regards Calle On Mon, 25 Mar 2019 at 14:04, Dave Caughey <caugh...@gmail.com> wrote: > Hi Khushboo, > > And then what's the process to upload the downloaded records into the > other database? > > Cheers, > Dave > > > On Mon, Mar 25, 2019 at 12:14 AM Khushboo Vashi < > khushboo.va...@enterprisedb.com> wrote: > >> >> >> On Fri, Mar 22, 2019 at 6:06 PM Dave Caughey <caugh...@gmail.com> wrote: >> >>> Sorry, for the basic question, but I'm not sure if there are bug(s) in >>> pgAdmin, or just that I'm clueless. (My money lies on the latter!) >>> >>> Imagine the scenario where you are adding a feature to a product that >>> requires adding some new rows to a configuration table, and as part of the >>> patch you need to replicate a bunch of record from your development >>> database to your production databases. >>> >>> You'd think there'd be a number of options, e.g., >>> >>> 1. After doing a "View/Edit..." | "Filter by", select the displayed >>> records, click "copy" to get them on to the clipboard, then go to the >>> production database, do a "View/Edit..." on the corresponding table, and >>> paste. But, in my case, I need my auto-sequenced "id" column to be omitted >>> (so it gets re-generated in the new table), so perhaps this isn't the right >>> choice. Even worse, over the years, my database tables (auto-created via >>> Hibernate used in a Java Servlet) no longer have the same column order. >>> (Question: is there no way that copy-and-paste between tables can consider >>> the column names so copying between (int id,int feature_id,text name) and >>> (int id,text name,int feature_id) is possible?) >>> >>> 2. Or, I could right-click on the table and use Import/Export..." >>> (Question: is there a way to filter the records that will get exported? >>> Or is there a way to trigger import/export on the results of a >>> "View/Edit..." | "Filter by"?). However, here the issue is the columns no >>> longer have the same order (e.g., (int,int,text) vs (int,text,int)) so >>> "Import/Export..." fails. (Question: Is that not what the "Header" toggle >>> is supposed to do? I see that enabling it during export *adds* a header >>> to the export files, but shouldn't enabling it during import cause it to be >>> used to identify the order?). This method has the attraction that I can >>> use the "Columns" tab to exclude one of the columns from my export (i.e., >>> my auto-sequenced "id" column). >>> >>> 3. Or, I could do a "Backup..." and then a corresponding "Restore..." , >>> but I noticed that there the generated file contains CREATE DATABASE bits >>> of code even though the "Include CREATE DATABASE" toggle in the Backup..." >>> dialog is set to "No" (Question: bug, or my misunderstanding?). But I'm >>> guessing that a backup/restore will generally do a complete and utter >>> restore, rather than just moving some data. >>> >>> 4. Other options? >>> >>> How about *Download as CSV* option? >> >>> So, what is the best/simplest way to copy data between tables, given the >>> possibility that some/all might apply? >>> >>> - The columns may be in a different order in different databases >>> - One column might need be left blank >>> - I only want to copy some of the records >>> >>> Cheers, >>> Dave >>> >> -- *Carl-Anders (Calle) Hedberg* HISP Researcher & Technical Specialist Health Information Systems Programme – South Africa Cell: +47 41461011 (Norway) Iridium SatPhone: +8816-315-19119 (usually OFF) E-mail1: ca...@hisp.org E-mail2: calle.hedb...@gmail.com Skype: calle_hedberg
