Re: How to: copy records from one table to another?

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:doug(at)artsman(dot)com
http://www.artsman.com
Phone (403) 650-1978

> On Mar 25, 2019, at 7:57 AM, Calle Hedberg <calle(dot)hedberg(at)gmail(dot)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 <doug(at)artsman(dot)com <mailto:doug(at)artsman(dot)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:doug(at)artsman(dot)com <mailto:doug(at)artsman(dot)com>
> http://www.artsman.com <http://www.artsman.com/>
> Phone (403) 650-1978
>
>> On Mar 25, 2019, at 7:29 AM, Calle Hedberg <calle(dot)hedberg(at)gmail(dot)com <mailto:calle(dot)hedberg(at)gmail(dot)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 <caugheyd(at)gmail(dot)com <mailto:caugheyd(at)gmail(dot)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(dot)vashi(at)enterprisedb(dot)com <mailto:khushboo(dot)vashi(at)enterprisedb(dot)com>> wrote:
>>
>>
>> On Fri, Mar 22, 2019 at 6:06 PM Dave Caughey <caugheyd(at)gmail(dot)com <mailto:caugheyd(at)gmail(dot)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: calle(at)hisp(dot)org <mailto:calle(at)hisp(dot)org>
>> E-mail2: calle(dot)hedberg(at)gmail(dot)com <mailto:calle(dot)hedberg(at)gmail(dot)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: calle(at)hisp(dot)org <mailto:calle(at)hisp(dot)org>
> E-mail2: calle(dot)hedberg(at)gmail(dot)com <mailto:calle(dot)hedberg(at)gmail(dot)com>
> Skype: calle_hedberg
>