hi Calle: love notepad ++ it works quite well for large files but 50gig would be large.
if you know the pattern you are going after, I have used SED to edit or replace the things I don’t like, especially if it its predictable. sed is standard unix stream editor (so available on mac and unix). on windows, I think this is the one I’ve used http://gnuwin32.sourceforge.net/packages/sed.htm <http://gnuwin32.sourceforge.net/packages/sed.htm> there are others. it makes playing with these files somewhat scriptable. Doug Easterbrook Arts Management Systems Ltd. mailto:d...@artsman.com http://www.artsman.com Phone (403) 650-1978 > On Mar 25, 2019, at 7:57 AM, Calle Hedberg <calle.hedb...@gmail.com> wrote: > > Doug, > > That's neat - I wasn't aware of that. Just tried it for a 500mb dump, and > it's quick and easy - the only slightly tricker part is to edit the sql. It > worked using Notepad++ for that 500MB .sql file, but less sure if it works if > the file is 5 or 50 GB... > > Anyway, nice to have it as an option. > > Regards > Calle > > On Mon, 25 Mar 2019 at 15:37, Doug Easterbrook <d...@artsman.com > <mailto:d...@artsman.com>> wrote: > 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 <mailto:d...@artsman.com> > http://www.artsman.com <http://www.artsman.com/> > Phone (403) 650-1978 > >> On Mar 25, 2019, at 7:29 AM, Calle Hedberg <calle.hedb...@gmail.com >> <mailto: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 >> > > > > -- > 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 >
