Skip site navigation (1) Skip section navigation (2)

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

From: ries van Twisk <pg(at)rvt(dot)dds(dot)nl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Ruzsinszky Attila <ruzsinszky(dot)attila(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org, Joe Conway <mail(at)joeconway(dot)com>
Subject: Re: [NOVICE] LATIN2->UTF8 conversation with dblink
Date: 2009-02-02 19:20:43
Message-ID: 8F5CC210-3E84-4557-AC93-5609FEB3B804@rvt.dds.nl (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novice


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

> Ruzsinszky Attila <ruzsinszky(dot)attila(at)gmail(dot)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




In response to

pgsql-novice by date

Next:From: rhubbellDate: 2009-02-02 19:50:38
Subject: calculating elapsed times between timestamps
Previous:From: Tom LaneDate: 2009-02-02 18:31:54
Subject: Re: [NOVICE] LATIN2->UTF8 conversation with dblink

pgsql-hackers by date

Next:From: Zdenek KotalaDate: 2009-02-02 19:42:36
Subject: [patch] fix for regression tests (locale cs_CZ)
Previous:From: David E. WheelerDate: 2009-02-02 19:03:29
Subject: Re: LIMIT NULL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group