Thread: pgAdmin2 UTF-8 support
Dear friends, PostgreSQL 7.2 offers extensive UTF-8 support. In case of an UTF-8 database, the big news seems that we now have to write function and view definitions in UTF-8. The problem is that pgAdmin2, HighlightBox (the colour code editor) and the result view do not support Unicode by now. And probably will not whithout further information. Questions : 1) Is it possible to convert functions/views to/from UTF-8/SQL_ASCII using server-side queries in PostgreSQL. $M cowboys do not offer tools to convert Unicode on the fly in VB. Is there something in PostgreSQL? to_ascii can convert from unicode to ascii. How can we do the converse? 2) Do you know if ODBC UCS-2 support would help us with pgAdmin2? Does anyone has extensive knowledge of UCS-2 support under VB? As far as I know, VB only supports reading UCS-2 files and displaying data in Hflexgrid. Any idea welcome, Cheers, Jean-Michel POURE
Dear Dave,
I used pgsql with PostgreSQL 7.2 and noticed there was an automatic
translation between UTF-8 <-> Latin characters. This is a new feature of
PostgreSQL 7.2.
So fine :
Suppose we have a Unicode database :
\Encoding Latin1 (\same with \Encoding SQL_ASCII does not work)
INSERT INTO table_foo (field_bar) VALUES ('é');
SELECT field_bar FROM table_foo WHERE field_bar='é' works.
I checked the UTF-8 display in Apache, it works.
In the case of an UTF-8 database, it means we need to create and query schema
objects using \Encoding Latin1. Should it be done at ODBC level or at
pgAdmin2 level? What is your opinion?
This could help us display non-multibyte UTF-8 data as well.
Cheers,
Jean-Michel POURE
Jean Michel We have a Unicode hebrew db in PG7.2. Uou dont need to write the functions and views in UTF-8 unless you have some literals inside the code. We had this issue with Hebrew; so we store all the literals as tokens in another table which may have some overhead but makes for great flexibility. None of our functions or views are coded in UTF-8 and they work just fine. If you have some functions that MUST be in UNICODE you might define them as triggers so that when VB does an action on the PG db - the trigger fires - that should give you transparent operation to VB. FWIW - the gnu recode function converts anything to anything. It can run on the server side - for example in plpgtcl procedures. BTW - you probably all know this but if you really want ORDER BY to work in Unicode - you should use glibc2.2 - ie. RH72. Best rgrds danny lieberman ----- Original Message ----- From: "Jean-Michel POURE" <jm.poure@freesurf.fr> To: "Hiroshi Inoue" <Inoue@tpf.co.jp> Cc: <pgsql-odbc@postgresql.org>; <pgadmin-hackers@postgresql.org> Sent: Sunday, February 24, 2002 11:39 PM Subject: [ODBC] pgAdmin2 UTF-8 support Dear friends, PostgreSQL 7.2 offers extensive UTF-8 support. In case of an UTF-8 database, the big news seems that we now have to write function and view definitions in UTF-8. The problem is that pgAdmin2, HighlightBox (the colour code editor) and the result view do not support Unicode by now. And probably will not whithout further information. Questions : 1) Is it possible to convert functions/views to/from UTF-8/SQL_ASCII using server-side queries in PostgreSQL. $M cowboys do not offer tools to convert Unicode on the fly in VB. Is there something in PostgreSQL? to_ascii can convert from unicode to ascii. How can we do the converse? 2) Do you know if ODBC UCS-2 support would help us with pgAdmin2? Does anyone has extensive knowledge of UCS-2 support under VB? As far as I know, VB only supports reading UCS-2 files and displaying data in Hflexgrid. Any idea welcome, Cheers, Jean-Michel POURE ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> We have a Unicode hebrew db in PG7.2. Uou dont need to write the functions > and views in UTF-8 unless you have some > literals inside the code. We had this issue with Hebrew; so we store all > the literals as tokens in another table which may have some overhead but > makes for great flexibility. None of our functions or views are coded in > UTF-8 and they work just fine. Dear Dany, This is very interesting. We are trying to add Hebrew support to pgAdmin2. In PostgreSQL 7.2 we found it was possible to SET CLIENT ENCODING TO LATIN1 and display data from a unicode database without problem (with French hyphens éèù, etc..). This was not possible in 7.1.x. It would of great help for us if you could install pgAdmin2 (http://pgadmin.postgresql.org) and do the following test. - connect to your UTF-8 database, - Enter SET CLIENT_CODING TO one of the following available at http://www.postgresql.org/idocs/index.php?multibyte.html. Maybe there are new encodings available. Recode can translate nearly all encodings. Can you see the Hebrew information? What font should be used for displaying Hebrew information in pgAdmin2? Could you send us a sample Hebrew table_dump (10 records). Cheers, Jean-Michel
Dear Danny, Could you try pgAdmin2 and enter 'SET CLIENT_ENCODING = ISO-8859-8'. Then try to display data in pgAdmin2. Does it work? Information can be found at http://developer.postgresql.org/docs/postgres/multibyte.html#AEN16680 Cheers, Jean-Michel POURE
Jean-Michel Hmm - interesting idea. We werent able to display or enter hebrew chars in Pgadmin2 because apparently the VB objects it uses dont support hebrew. Let me try and I'll get back to you real soon danny