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

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> 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 <doug(at)artsman(dot)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>
> 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> 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> wrote:
>>
>>>
>>>
>>> On Fri, Mar 22, 2019 at 6:06 PM Dave Caughey <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
>
> E-mail2: 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

E-mail2: calle(dot)hedberg(at)gmail(dot)com

Skype: calle_hedberg