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
phead...@linxco-inc.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 <caugh...@gmail.com
<mailto:caugh...@gmail.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.hedb...@gmail.com <mailto: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
<mailto: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 <mailto:ca...@hisp.org>
E-mail2:calle.hedb...@gmail.com <mailto:calle.hedb...@gmail.com>
Skype: calle_hedberg
