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

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> 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>
> 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> 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
>>
>> E-mail2: calle(dot)hedberg(at)gmail(dot)com
>>
>> Skype: calle_hedberg
>>
>