Re: How to: copy records from one table to another? - Mailing list pgadmin-support
From | richard coleman |
---|---|
Subject | Re: How to: copy records from one table to another? |
Date | |
Msg-id | CAGA3vBuDLxXxYSHEFnirqgm4gaz7NbfDxBT0zJhFciXgk5pczw@mail.gmail.com Whole thread Raw |
In response to | How to: copy records from one table to another? (Dave Caughey <caugheyd@gmail.com>) |
List | pgadmin-support |
Dave,
If you are talking about copying records between tables in different databases, postgresql currently doesn't support this. If you are talking about within the same database, 'INSERT with a SELECT CLAUSE', 'SELECT INTO', and similar commands exist.
As far as I know;
#1 Sounds good in theory, doesn't really work in practice so I can see why you can't do it in pgAdmin.
#2 That would be a cool feature. You can already specify the column(s) to export. Perhaps you want to put in a feature request.
#3.Yep. that's my understanding too. Like using dynamite to remove a splinter.
#4 Use SQL Statements designed for this in a query window (see above).
In the end pgAdmin is nice but no substitute for some good ol' fashioned SQL. Just open up a query tool window and go to town.
I hope that helps,
rik.
On Fri, Mar 22, 2019 at 8:36 AM Dave Caughey <caugheyd@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?Cheers,
- 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
Dave
pgadmin-support by date: