How do I copy part of table from db1 to db2 (and rename the columns)?

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
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!

# 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

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

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

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

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.

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

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

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

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.