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

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

# 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
bytesequences 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
invalidbyte 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.

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.