Re: [HACKERS] [NOVICE] LATIN2->UTF8 conversation with dblink

[ries van Twisk]

On Feb 2, 2009, at 1:31 PM, Tom Lane wrote:

Ruzsinszky Attila <ruzsinszky.att...@gmail.com> writes:
The situation:
We've got two machines. The source database (DB) is running on an  
RHEL
5.x machine
with PSQL 8.1.11. The destination DB is running on SuSE 11.0 with  
PSQL 8.3.x.
The mechines are relative far away each other and there is a 2Mbps  
WAN
line between them.

The DB is the same except the character coding. Source is LATIN2 and
the target DB is UTF8.
We wrote a trigger to copy the data from source to target with  
dblink.
The problem is the
different DB character coding! PGSQL complains about wrong byte  
order.

Hmm.  You can presumably fix this by setting client_encoding in the
dblink connection to match the encoding in use in the database it's
called in.  But I wonder why dblink doesn't just do that for you
automatically.

                        regards, tom lane



We did it like this:

INSERT INTO a tbl_datafeed
SELECT
nextval('acc_mkt.tbl_ants_to_ace_feed_row_id_seq'),
convert(project_number::bytea, 'WIN1258'::text, 'UTF8'::text),
convert(project_name::bytea, 'WIN1258'::text, 'UTF8'::text),
...
....
...
 FROM dblink('dbname=mydbname host=removehost user=someuser','SELECT  
* FROM tbl_datafeed') AS p
(
...
..
...
.)

Ries




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers