not sure if its been mentioned.
pg_dump -v -t (the specific table you want) which gives you the data plus the table create plus the sql edit sql if need be psql -d newdatabase < theFileAbove Doug Easterbrook Arts Management Systems Ltd. mailto:d...@artsman.com http://www.artsman.com Phone (403) 650-1978 > On Mar 25, 2019, at 7:29 AM, Calle Hedberg <calle.hedb...@gmail.com> wrote: > > 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 > <mailto: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 <mailto:khushboo.va...@enterprisedb.com>> > wrote: > > > On Fri, Mar 22, 2019 at 6:06 PM Dave Caughey <caugh...@gmail.com > <mailto: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 <mailto:ca...@hisp.org> > E-mail2: calle.hedb...@gmail.com <mailto:calle.hedb...@gmail.com> > Skype: calle_hedberg >
