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

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
>