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

[Patrick Headley]

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 > 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
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  (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 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 

Re: pgadmin4 usage as of web proxy for developers

[Dave Page]

Hi

On Fri, Mar 22, 2019 at 4:29 PM Mickael Curty  wrote:

> Good afternoon,
>
> As the new pgadmin (pgadmin4) rely on web browser for the display,  I
> wanted to use it like a 'phpMyAdmin', .. for postgresql, in a replacement
> of phppgadmin.
>
> But its user management make it hard (maybe I'm wrong about that) to plug
> it to a corporate ldap authentication and preconfigure server that users
> will use with their own credentials.
>
> Despite I know how to plug our active directory servers to PAM, there are
> 2 points to handle :
> - how to make pam the way to authenticate users
> - how to let users use their own credentials for pre configured servers -
> I'd like them to use their own user instead of configuring servers with
> mutualized users.
>
> Is there something planned to make this possible ?
> or is it already possible to work this way ?
>

It's planned, but not yet implemented:
https://redmine.postgresql.org/issues/2186


>
> I found no info over internet about this.
>
> Thank you very much for some help :)
>
> here are the step I used for my 'server side' installation of pgadmin 4,
> for information.
>
> https://www.digitalocean.com/community/tutorials/how-to-install-configure-pgadmin4-server-mode
>
>
>
>
>

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

pgadmin4 usage as of web proxy for developers

[Mickael Curty]

Good afternoon,

As the new pgadmin (pgadmin4) rely on web browser for the display,  I
wanted to use it like a 'phpMyAdmin', .. for postgresql, in a replacement
of phppgadmin.

But its user management make it hard (maybe I'm wrong about that) to plug
it to a corporate ldap authentication and preconfigure server that users
will use with their own credentials.

Despite I know how to plug our active directory servers to PAM, there are 2
points to handle :
- how to make pam the way to authenticate users
- how to let users use their own credentials for pre configured servers -
I'd like them to use their own user instead of configuring servers with
mutualized users.

Is there something planned to make this possible ?
or is it already possible to work this way ?

I found no info over internet about this.

Thank you very much for some help :)

here are the step I used for my 'server side' installation of pgadmin 4,
for information.
https://www.digitalocean.com/community/tutorials/how-to-install-configure-pgadmin4-server-mode

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

[Dave Caughey]

Yeah, similarly, for complicated migrations that impact multiple tables
(e.g., needing to migrating FK's), I'll actually add a migration method to
my admin Java servlet, because I can then run it in a debug mode (no
commits) and have as much logging as I need to make sure that the data will
be migrated correctly.  For less complicated ones, I'll just write a little
Perl script that iterates over my production databases and applies the same
change to each.   But for simple situations of just needing to copy rows
from one table to another database, I'd been hoping there was a faster way
than actually having to write some Java or Perl.

Cheers,
Dave


On Fri, Mar 22, 2019 at 10:34 AM richard coleman <
rcoleman.ascen...@gmail.com> 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  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 
>> 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  (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  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 

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

[richard coleman]

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  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 
> 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  (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  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
>>
>

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

[Dave Caughey]

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 
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  (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  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
>

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

[richard coleman]

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  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
>

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

[Calle Hedberg]

Dave,

You are talking about "copying" data from one table to another table in the
same database, yes?

For that use an INSERT INTO  (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  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

How to: copy records from one table to another?

[Dave Caughey]

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

Unable to create a server

[ado_ffm]

Hi to all,

Can someone explain me the following error ... unfortunately I am not able
to create a new server.

2019-03-22 08:43:59,581: ERROR  pgadmin:'ascii' codec can't encode 
character
u'\xfc' in position 33: ordinal not in range(128)
Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/pgadmin4-web/flask/app.py", line
1612, in full_dispatch_request
rv = self.dispatch_request()
  File "/usr/lib/python2.7/site-packages/pgadmin4-web/flask/app.py", line
1598, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/lib/python2.7/site-packages/pgadmin4-web/flask/views.py", line
84, in view
return self.dispatch_request(*args, **kwargs)
  File
"/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/browser/utils.py",
line 259, in dispatch_request
return method(*args, **kwargs)
  File
"/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/browser/server_groups/servers/__init__.py",
line 1069, in connect
.format(server.id, server.name, errmsg)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xfc' in position
33: ordinal not in range(128)

Thanks in advance

Achim



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html