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

[Isaias Sanchez]

The first post have lot's of constraint about the ID and different order 
of columns, you can use copy of select's to do that stuff very easy with 
psql.

Example:
server_origin, db_origin, table1 (id integer, data1 text, data2 numeric, 
data3 jsonb)

server_destination, db_destination, table2(id integer, data4 jsonb, 
data5 numeric)

You need to move only part of the data from table1 to table2 (data2 -> 
data5 and data3 -> data4) and also filtered, you can do this:

Enter to server_destination:

psql -h server_origin -d db_origin -U anyrouser -c "copy (select data3, 
data2 from table1 where data1 = 'VALID') to stdout" | psql -d 
db_destination -c "copy table2 (data4, data5) from stdin"

Cheers,

Isaias S.

On 25/3/19 14:37, Doug Easterbrook 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
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