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.hedb...@gmail.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 <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? >> >> 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 > > E-mail2: calle.hedb...@gmail.com > > Skype: calle_hedberg >
