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

Dave,

I think the easiest way to copy the data from one Database to another is
over foreigen data Wrappers. You could insert the configuration tables
of your production database in your dev database and then simply copy
the data with insert into (...) Select from ... Command, fast and easy

Greetings

Jürgen Spatz

jurgen(dot)spatz(at)godesys(dot)de

Am 22.03.2019 um 18:34 schrieb Patrick Headley:
> You could also use MS Access if you already have it. It can connect to
> multiple databases and allow you to run an INSERT query that can pull
> from one DB and save to the other.
>
> */Patrick Headley/*
> Linx Consulting, Inc.
> (303) 916-5522
> pheadley(at)linxco-inc(dot)com
> www.linxco-inc.com
> On 3/22/19 8:34 AM, richard coleman wrote:
>> Dave,
>>
>> When moving data *between* postgresql databases, I rely on custom
>> python scripts using psycopg2. A simple write loop inside a read
>> loop and two connections usually does the trick.
>>
>> rik.
>>
>> On Fri, Mar 22, 2019 at 9:59 AM Dave Caughey <caugheyd(at)gmail(dot)com
>> <mailto:caugheyd(at)gmail(dot)com>> wrote:
>>
>> Unfortunately, using simple SQL statements isn't an option when
>> dealing with multiple databases (e.g., moving records from a
>> development environment into a production system as per my
>> proposed use case).
>>
>> Cheers,
>> Dave
>>
>>
>> On Fri, Mar 22, 2019 at 9:18 AM Calle Hedberg
>> <calle(dot)hedberg(at)gmail(dot)com <mailto:calle(dot)hedberg(at)gmail(dot)com>> wrote:
>>
>> Dave,
>>
>> You are talking about "copying" data from one table to
>> another table in the same database, yes?
>>
>> For that use an INSERT INTO <destinationtable> (SELECT
>> .....FROM sourcetable) query - it will provide most of the
>> flexibility you need with regard to including/excluding
>> columns, renaming, changing column order, etc.
>>
>> If you need to do several sequential manipulation steps on
>> the data to be copied, it might be necessary to first insert
>> the data into a temporary table, then do whatever
>> manipulations necessary, before inserting the final result
>> into the destination table (or if not absolutely necessary,
>> at least easier - while advanced manipulation can be done in
>> a single step, it might require advanced skills and a crystal
>> clear understanding of the underlying processes, and doing it
>> step by step can be safer/easier for us normal mortals...)
>>
>> Regards
>> Calle
>>
>> On Fri, 22 Mar 2019 at 13:36, 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?
>>
>> 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
>>
>