Dblink and character conversions?

[polk3]

Hey,

I've been playing around with dblink a bit, created an empty PT4 db and wanted to view/copy some data from PT3 with it.

Works very well, except when there is a euro character in one of the fields. Then the field is blank. Would anyone have any idea as to why this is? I've been using something like this:

select limit_name from dblink('dbname=''PT3 DB'' password=xxxx', 'select limit_name from omaha_limit where limit_currency = ''EUR''') as t1( limit_name text );

I figured it could very likely be related to different character sets used. If I make limit name be a bytea field I get:

\2000.10 PL Hi

whereas I would expect:

€0.10 PL Hi

Is there an explicit character conversion I should use, or is this more complicated than that?

[kraada]

I'm afraid I don't know anything about dblink, but I do know that we are using unicode characters - might that make a difference for you?

[polk3]

Yes, that might be helpful. PT3 and PT4 uses the same character encoding you mean? Maybe postgresql is trying to do a character conversion (because of SQL_ASCII vs UTF-8 db's) that it shouldn't do?

I'll be happy to post some other dblink experiences as I gain them if anyone's interested. So far I've only been doing simple things, but things like cross db joins and unions seem to be working quite well and with acceptable performance.

Oh, postgresql version is 9.0.8 btw though I don't know if that makes a ton of difference.

[polk3]

Ok, turns out I was running into both character conversion and typecasting issues. I was trying to use convert() to get it right, but then dug a little deeper. When the solution presents itself it looks incredibly simple:

select convert_from(limit_name::bytea, 'WIN1252') from dblink('dbname=''PT3 DB'' password=xxxx', 'select limit_name from omaha_limit where limit_currency = ''EUR''') as t1( limit_name text );

[polk3]

Btw, here's a potentially more useful example, which generates a diff list between the PT3 and PT4 limit tables, for both holdem and omaha:

Code: Select all

select * from
(select * from dblink('dbname=''PT4 DB'' password=xxxx',
'select limit_name, id_gametype, limit_currency, amt_bb, flg_nlpl, flg_lo from cash_limit')
as t1( limit_name text,id_gametype smallint, limit_currency character(3), amt_bb numeric(7,2), flg_nlpl boolean, flg_lo boolean )

except

select convert_from(limit_name::bytea, 'WIN1252') as limit_name, id_gametype, limit_currency, amt_bb, flg_nlpl, flg_lo from dblink('dbname=''PT3 DB'' password=xxxx',
'select limit_name, 1 as id_gametype, limit_currency, amt_bb, flg_nlpl, false as flg_lo from holdem_limit
union
select limit_name, case when flg_lo then 3 else 2 end as id_gametype, limit_currency, amt_bb, flg_nlpl, flg_lo from omaha_limit')
as t1( limit_name text,id_gametype smallint, limit_currency character(3), amt_bb numeric(7,2),
flg_nlpl boolean, flg_lo boolean ))
as tmp1

union

(select convert_from(limit_name::bytea, 'WIN1252') as limit_name, id_gametype, limit_currency, amt_bb, flg_nlpl, flg_lo from dblink('dbname=''PT3 DB'' password=xxxx',
'select limit_name, 1 as id_gametype, limit_currency, amt_bb, flg_nlpl, false as flg_lo from holdem_limit
union
select limit_name, case when flg_lo then 3 else 2 end as id_gametype, limit_currency, amt_bb, flg_nlpl, flg_lo from omaha_limit')
as t1( limit_name text,id_gametype smallint, limit_currency character(3), amt_bb numeric(7,2),
flg_nlpl boolean, flg_lo boolean )

except

select * from dblink('dbname=''PT4 DB'' password=xxxx',
'select limit_name, id_gametype, limit_currency, amt_bb, flg_nlpl, flg_lo from cash_limit')
as t1( limit_name text,id_gametype smallint, limit_currency character(3), amt_bb numeric(7,2), flg_nlpl boolean, flg_lo boolean ))

order by limit_name
;