From: | Isaias Sanchez <isaias(dot)sanchez(dot)l(at)gmail(dot)com> |
---|---|
To: | pgadmin-support(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to: copy records from one table to another? |
Date: | 2019-03-25 14:55:15 |
Message-ID: | 7911bbdf-f8d5-603e-347c-010ab07488b5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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: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
>> <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
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Calle Hedberg | 2019-03-25 14:57:34 | Re: How to: copy records from one table to another? |
Previous Message | Doug Easterbrook | 2019-03-25 14:37:46 | Re: How to: copy records from one table to another? |