pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I copy part of table from db1 to db2 (and rename the columns)?
11+ messages / 7 participants
[nested] [flat]
* How do I copy part of table from db1 to db2 (and rename the columns)?
@ 2005-08-31 10:00 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:15 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Kenneth Gonsalves <lawgon@thenilgiris.com>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 13:19 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 3 replies; 11+ messages in thread
From: Joost Kraaijeveld @ 2005-08-31 10:00 UTC (permalink / raw)
To: Pgsql-sql@postgresql.org, Pgsql-General <pgsql-general@postgresql.org>
Hi,
I want to copy several columns of a source table from db1 to db2, and
create the target table and rename the columns in the process.
Is that possible in PostgresQL? If so, an example or url for such a
command /script would be appreciated...
TIA
Joost
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do I copy part of table from db1 to db2 (and rename the columns)?
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-08-31 10:15 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2 siblings, 0 replies; 11+ messages in thread
From: Kenneth Gonsalves @ 2005-08-31 10:15 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 31 Aug 2005 3:30 pm, Joost Kraaijeveld wrote:
> Hi,
>
> I want to copy several columns of a source table from db1 to db2,
> and create the target table and rename the columns in the process.
>
> Is that possible in PostgresQL? If so, an example or url for such a
> command /script would be appreciated...
checkout dblink
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do I copy part of table from db1 to db2 (and rename the columns)?
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-08-31 10:40 ` Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 11+ messages in thread
From: Roman Neuhauser @ 2005-08-31 10:40 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Pgsql-sql@postgresql.org, Pgsql-General <pgsql-general@postgresql.org>
# J.Kraaijeveld@Askesis.nl / 2005-08-31 12:00:30 +0200:
> I want to copy several columns of a source table from db1 to db2, and
> create the target table and rename the columns in the process.
>
> Is that possible in PostgresQL? If so, an example or url for such a
> command /script would be appreciated...
check these man pages: pg_dump(1), pg_restore(1), alter_table(7)
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do I copy part of table from db1 to db2 (and
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
@ 2005-08-31 12:00 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 12:09 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
2005-08-31 12:47 ` Re: How do I copy part of table from db1 to db2 (and Tino Wildenhain <tino@wildenhain.de>
2005-08-31 14:29 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 11+ messages in thread
From: Joost Kraaijeveld @ 2005-08-31 12:00 UTC (permalink / raw)
To: Roman Neuhauser <neuhauser@sigpipe.cz>; +Cc: pgsql-sql; Pgsql-General <pgsql-general@postgresql.org>
On Wed, 2005-08-31 at 12:40 +0200, Roman Neuhauser wrote:
> check these man pages: pg_dump(1), pg_restore(1), alter_table(7)
I am afraid that the problem is more complex. The original database (which is created with SQL_ASCII) contains invalid byte sequences in some columns (target database created with UNICODE):
jkr@Panoramix:~/postgresql$ pg_dump -t artik munttest | psql muntfinal
> output.txt
ERROR: invalid byte sequence for encoding "UNICODE": 0xeb207a
CONTEXT: COPY artik, line 11705, column omschrijving: "Anna v. Groot
Brittannië zi prf 38.61 mm"
So I cannot dump/restore/alter table. I was hoping that piping the text
from stdout to psql that a valid conversion to unicode would take place
but apparently that is not the case.
Any other ideas?
Joost
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How do I copy part of table from db1 to db2 (and
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-08-31 12:09 ` Thomas Pundt <mlists@rp-online.de>
2005-08-31 12:14 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
2 siblings, 1 reply; 11+ messages in thread
From: Thomas Pundt @ 2005-08-31 12:09 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; Pgsql-General <pgsql-general@postgresql.org>
On Wednesday 31 August 2005 14:00, Joost Kraaijeveld wrote:
| So I cannot dump/restore/alter table. I was hoping that piping the text
| from stdout to psql that a valid conversion to unicode would take place
| but apparently that is not the case.
|
| Any other ideas?
maybe the "recode" utility can help then? Something like
pg_dump -t artik munttest | recode latin1..utf | psql muntfinal
Ciao,
Thomas
--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How do I copy part of table from db1 to db2 (and
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 12:09 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
@ 2005-08-31 12:14 ` Thomas Pundt <mlists@rp-online.de>
2005-08-31 15:02 ` Re: [SQL] How do I copy part of table from db1 to db2 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 11+ messages in thread
From: Thomas Pundt @ 2005-08-31 12:14 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; Pgsql-General <pgsql-general@postgresql.org>
On Wednesday 31 August 2005 14:09, Thomas Pundt wrote:
| maybe the "recode" utility can help then? Something like
|
| pg_dump -t artik munttest | recode latin1..utf | psql muntfinal
sorry to follow up on myself, but that command should read
pg_dump -t artik munttest | recode latin1..utf8 | psql muntfinal
^^^^
(utf is not a valid "recode" charset, utf8 is).
Ciao,
Thomas
--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How do I copy part of table from db1 to db2
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 12:09 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
2005-08-31 12:14 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
@ 2005-08-31 15:02 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 11+ messages in thread
From: Joost Kraaijeveld @ 2005-08-31 15:02 UTC (permalink / raw)
To: Thomas Pundt <mlists@rp-online.de>; +Cc: pgsql-sql; Pgsql-General <pgsql-general@postgresql.org>
On Wed, 2005-08-31 at 14:14 +0200, Thomas Pundt wrote:
> pg_dump -t artik munttest | recode latin1..utf8 | psql muntfinal
Because the source encoding is unknown (the actual source database was
an ODBC source without known encoding that was copied with a C++ written
to a SQL_ASCII PostgreSQL database) I used no source encoding:
"pg_dump -t artik munttest | recode ..utf8 | psql muntfinal"
and that worked: no errors. I just lost all diacritical chars as far as
I can see (which is a minor and someone else's problem ;-)).
Thanks for the sugggestion.
Joost
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do I copy part of table from db1 to db2 (and
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-08-31 12:47 ` Tino Wildenhain <tino@wildenhain.de>
2 siblings, 0 replies; 11+ messages in thread
From: Tino Wildenhain @ 2005-08-31 12:47 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Roman Neuhauser <neuhauser@sigpipe.cz>; pgsql-sql; Pgsql-General <pgsql-general@postgresql.org>
Joost Kraaijeveld schrieb:
> On Wed, 2005-08-31 at 12:40 +0200, Roman Neuhauser wrote:
>
>> check these man pages: pg_dump(1), pg_restore(1), alter_table(7)
>
>
> I am afraid that the problem is more complex. The original database (which is created with SQL_ASCII) contains invalid byte sequences in some columns (target database created with UNICODE):
>
> jkr@Panoramix:~/postgresql$ pg_dump -t artik munttest | psql muntfinal
>
>>output.txt
>
> ERROR: invalid byte sequence for encoding "UNICODE": 0xeb207a
> CONTEXT: COPY artik, line 11705, column omschrijving: "Anna v. Groot
> Brittannië zi prf 38.61 mm"
>
> So I cannot dump/restore/alter table. I was hoping that piping the text
> from stdout to psql that a valid conversion to unicode would take place
> but apparently that is not the case.
>
> Any other ideas?
If you know the implicit charset you used in your SQL_ASCII
db, try with: SET client_encoding TO 'the_charset';
In your import script if you use one.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How do I copy part of table from db1 to db2 (and
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-08-31 14:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-31 15:14 ` Re: [SQL] How do I copy part of table from db1 to db2 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-08-31 14:29 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Roman Neuhauser <neuhauser@sigpipe.cz>; pgsql-sql; Pgsql-General <pgsql-general@postgresql.org>
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> I am afraid that the problem is more complex. The original database
> (which is created with SQL_ASCII) contains invalid byte sequences in
> some columns (target database created with UNICODE):
There is no magic bullet to make bad data better. If the original data
is all in a specific encoding that happens not to be unicode, then you
can get Postgres to translate it for you --- just edit the dump file and
change CLIENT_ENCODING to the real original encoding before reloading.
If, as seems more likely, there's a mishmash of different encodings then
you are in for some pain. At the minimum you'll have to separate out
the rows that are in each encoding so you can pass them through
different conversion processes.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How do I copy part of table from db1 to db2
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:40 ` Re: How do I copy part of table from db1 to db2 (and rename the columns)? Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 14:29 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-31 15:14 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 11+ messages in thread
From: Joost Kraaijeveld @ 2005-08-31 15:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Roman Neuhauser <neuhauser@sigpipe.cz>; pgsql-sql; Pgsql-General <pgsql-general@postgresql.org>
On Wed, 2005-08-31 at 10:29 -0400, Tom Lane wrote:
> Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> If, as seems more likely, there's a mishmash of different encodings then
> you are in for some pain. At the minimum you'll have to separate out
Yep. The original database (which is copied to an SQL-ASCII PostgreSQL
database) is a mishmash of encodings. Actually no official encoding is
given for the database.
But I managed to get an acceptable (for me that is) import, only losing
all the diacritical chars for this moment (see other mail)
Thanks for responding,
Joost.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do I copy part of table from db1 to db2 (and rename the columns)?
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-08-31 13:19 ` Greg Sabino Mullane <greg@turnstep.com>
2 siblings, 0 replies; 11+ messages in thread
From: Greg Sabino Mullane @ 2005-08-31 13:19 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I want to copy several columns of a source table from db1 to db2, and
> create the target table and rename the columns in the process.
Ignoring the db part for now, you can do the first part of your request
like this:
CREATE TABLE mytable2 AS SELECT mycol AS newcolname, id, foo AS foo2
FROM mytable WHERE id > 100;
Then pg_dump -t mytable2 and pg_restore, as the rest of this thread indicates.
Note that you'll lose any triggers, indexes, etc. so only use this if
all you really want is the table. Otherwise, it's probably easiest to
pg_dump the whole database and then drop/rename columns in the new
database.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200508310915
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEARECAAYFAkMVrhIACgkQvJuQZxSWSsg3ZwCgs5kG/5P+RFr315+v3ia0g+Tr
bAEAoKHfRhJYynxVqyo/U06WaSeDXh3s
=hZIK
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-08-31 15:14 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-31 10:00 How do I copy part of table from db1 to db2 (and rename the columns)? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 10:15 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2005-08-31 10:40 ` Roman Neuhauser <neuhauser@sigpipe.cz>
2005-08-31 12:00 ` Re: How do I copy part of table from db1 to db2 (and Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 12:09 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
2005-08-31 12:14 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Thomas Pundt <mlists@rp-online.de>
2005-08-31 15:02 ` Re: [SQL] How do I copy part of table from db1 to db2 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 12:47 ` Re: How do I copy part of table from db1 to db2 (and Tino Wildenhain <tino@wildenhain.de>
2005-08-31 14:29 ` Re: [SQL] How do I copy part of table from db1 to db2 (and Tom Lane <tgl@sss.pgh.pa.us>
2005-08-31 15:14 ` Re: [SQL] How do I copy part of table from db1 to db2 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-08-31 13:19 ` Greg Sabino Mullane <greg@turnstep.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox