Thread: Adding German Character Set to PostgresSQL
Hi,
I am using psql (8.2.15) and I would like to input German characters (e.g. ä,ß,ö) into char fields I have in a database. I am having trouble getting the CENTOS Linux OS I am using to input German characters via a (apparently supported) German Keyboard Layout. However, that might be a separate matter. When I typed the German into Notepad in Windows and attempted to cut and paste the words into an INSERT statement, the characters do not persist:
Daß becomes DaDa and Heißt becomes HeiHeit which falls short of what I was hoping for.
I am wondering if I need to enable an international character set within Postgres before the German characters will input properly? If so, it’s not clear from the documentation I have attempted to find how one enables other characters sets within Postgres? Any suggestions will be much appreciated. Thank you.
Hagen Finley
Boulder, CO
On 01/02/2012 11:13 AM, Hagen Finley wrote: > Hi, > > I am using psql (8.2.15) and I would like to input German characters I am going to assume you are using a Postgresql 8.2.15 server(psql is the client program for Postgres, I am being pedantic because it reduces the confusion level:) ) > (e.g. ä,ß,ö) into char fields I have in a database. I am having trouble > getting the CENTOS Linux OS I am using to input German characters via a > (apparently supported) German Keyboard Layout. However, that might be a > separate matter. When I typed the German into Notepad in Windows and > attempted to cut and paste the words into an INSERT statement, the > characters do not persist: Not sure it would help, but if I had a choice I would use Wordpad. Notepad tends to fairly brain-dead when handling text. > > Daß becomes DaDa and Heißt becomes HeiHeit which falls short of what I > was hoping for. > > I am wondering if I need to enable an international character set within > Postgres before the German characters will input properly? If so, it’s > not clear from the documentation I have attempted to find how one > enables other characters sets within Postgres? Any suggestions will be > much appreciated. Thank you. Using psql do a \l at the prompt. That will show what encoding the database was setup with. > > Hagen Finley > > Boulder, CO > Thanks, -- Adrian Klaver adrian.klaver@gmail.com
Thanks Adrian,
Looks like I am currently using UTF8:
gpdemo=# \encoding
UTF8
And it looks like UTF8 doesn’t include the German characters I seek. Can someone explain how I can switch to 0000-0FFF which looks like the Basic Multilingual Plane Unicode which does include the characters I want?
Hagen
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, January 02, 2012 12:40 PM
To: Hagen Finley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Adding German Character Set to PostgresSQL
On 01/02/2012 11:13 AM, Hagen Finley wrote:
> Hi,
>
> I am using psql (8.2.15) and I would like to input German characters
I am going to assume you are using a Postgresql 8.2.15 server(psql is the client program for Postgres, I am being pedantic because it reduces the confusion level:) )
> (e.g. ä,ß,ö) into char fields I have in a database. I am having
> trouble getting the CENTOS Linux OS I am using to input German
> characters via a (apparently supported) German Keyboard Layout.
> However, that might be a separate matter. When I typed the German into
> Notepad in Windows and attempted to cut and paste the words into an
> INSERT statement, the characters do not persist:
Not sure it would help, but if I had a choice I would use Wordpad.
Notepad tends to fairly brain-dead when handling text.
>
> Daß becomes DaDa and Heißt becomes HeiHeit which falls short of what I
> was hoping for.
>
> I am wondering if I need to enable an international character set
> within Postgres before the German characters will input properly? If
> so, it’s not clear from the documentation I have attempted to find how
> one enables other characters sets within Postgres? Any suggestions
> will be much appreciated. Thank you.
Using psql do a \l at the prompt. That will show what encoding the database was setup with.
>
> Hagen Finley
>
> Boulder, CO
>
Thanks,
--
Adrian Klaver
Hello
2012/1/2 Hagen Finley <finhagen@comcast.net>:
> Thanks Adrian,
>
>
>
> Looks like I am currently using UTF8:
>
>
>
> gpdemo=# \encoding
>
> UTF8
>
>
>
> And it looks like UTF8 doesn’t include the German characters I seek. Can
> someone explain how I can switch to 0000-0FFF which looks like the Basic
> Multilingual Plane Unicode which does include the characters I want?
>
I am sure so it has
postgres=# create table xx(a text);
ERROR: relation "xx" already exists
postgres=# create table x(a text);
CREATE TABLE
postgres=# insert into x values('ä,ß,ö');
INSERT 0 1
postgres=# select * from x;
a
-------
ä,ß,ö
(1 row)
postgres=# \encoding
UTF8
postgres=#
Windows console doesn't work well with UTF8 - use pgAdmin there
regards
Pavel Stehule
>
>
> Hagen
>
>
>
>
>
>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
> Sent: Monday, January 02, 2012 12:40 PM
> To: Hagen Finley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Adding German Character Set to PostgresSQL
>
>
>
> On 01/02/2012 11:13 AM, Hagen Finley wrote:
>
>> Hi,
>
>>
>
>> I am using psql (8.2.15) and I would like to input German characters
>
>
>
> I am going to assume you are using a Postgresql 8.2.15 server(psql is the
> client program for Postgres, I am being pedantic because it reduces the
> confusion level:) )
>
>
>
>> (e.g. ä,ß,ö) into char fields I have in a database. I am having
>
>> trouble getting the CENTOS Linux OS I am using to input German
>
>> characters via a (apparently supported) German Keyboard Layout.
>
>> However, that might be a separate matter. When I typed the German into
>
>> Notepad in Windows and attempted to cut and paste the words into an
>
>> INSERT statement, the characters do not persist:
>
>
>
> Not sure it would help, but if I had a choice I would use Wordpad.
>
> Notepad tends to fairly brain-dead when handling text.
>
>
>
>
>
>>
>
>> Daß becomes DaDa and Heißt becomes HeiHeit which falls short of what I
>
>> was hoping for.
>
>>
>
>> I am wondering if I need to enable an international character set
>
>> within Postgres before the German characters will input properly? If
>
>> so, it’s not clear from the documentation I have attempted to find how
>
>> one enables other characters sets within Postgres? Any suggestions
>
>> will be much appreciated. Thank you.
>
>
>
> Using psql do a \l at the prompt. That will show what encoding the database
> was setup with.
>
>
>
>>
>
>> Hagen Finley
>
>>
>
>> Boulder, CO
>
>>
>
>
>
> Thanks,
>
> --
>
> Adrian Klaver
>
> adrian.klaver@gmail.com
Hagen, > gpdemo=# \encoding > UTF8 UTF8 includes virtually all characters you will need for any purpose on the earth. But: you showed the output of \encoding when you were asked to show \l . There is a subtle difference: \encoding shows the encoding of the connection between psql and the server; while \l shows the encoding the database server will actually store the data in. While the first can easily be changed for a session, the latter can only be set when creating a database (or a cluster). -hannes
On Monday, January 02, 2012 12:25:26 pm Hagen Finley wrote: > Thanks Adrian, > > > > Looks like I am currently using UTF8: > > > > gpdemo=# \encoding > > UTF8 Well that shows the client encoding not the server encoding. For that either do \l or show server_encoding; > > > > And it looks like UTF8 doesnt include the German characters I seek. Can > someone explain how I can switch to 0000-0FFF which looks like the Basic > Multilingual Plane Unicode which does include the characters I want? It should. > > > > Hagen > > > -- Adrian Klaver adrian.klaver@gmail.com
Hannes,
The output of \l is:
gpdemo=# \l
List of databases
Name | Owner | Encoding | Access privileges
------------+---------+----------+---------------------
acn | gpadmin | UTF8 |
gpdemo | gpadmin | UTF8 |
philosophy | gpadmin | UTF8 |
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin
It would be easy enough to create a new database with a different encoding -
only one record in the one in question. However, it sounded as though you
don't believe that is the issue - that UTF8 ought to support the German
characters I want. Am I understanding you correctly?
Hagen
-----Original Message-----
From: Hannes Erven [mailto:hannes@erven.at]
Sent: Monday, January 02, 2012 1:53 PM
To: pgsql-general@postgresql.org
Cc: finhagen@comcast.net
Subject: Re: [GENERAL] Adding German Character Set to PostgresSQL
Hagen,
> gpdemo=# \encoding
> UTF8
UTF8 includes virtually all characters you will need for any purpose on the
earth.
But: you showed the output of \encoding when you were asked to show \l .
There is a subtle difference:
\encoding shows the encoding of the connection between psql and the server;
while \l shows the encoding the database server will actually store the data
in.
While the first can easily be changed for a session, the latter can only be
set when creating a database (or a cluster).
-hannes
On Monday, January 02, 2012 1:47:13 pm Hagen Finley wrote: > Hannes, > > The output of \l is: > > gpdemo=# \l > List of databases > Name | Owner | Encoding | Access privileges > ------------+---------+----------+--------------------- > acn | gpadmin | UTF8 | > gpdemo | gpadmin | UTF8 | > philosophy | gpadmin | UTF8 | > postgres | gpadmin | UTF8 | > template0 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin > template1 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin > > It would be easy enough to create a new database with a different encoding > - only one record in the one in question. However, it sounded as though > you don't believe that is the issue - that UTF8 ought to support the > German characters I want. Am I understanding you correctly? So now we have established where it is going to. Now to find out where it is coming from. You said the server is running on Centos and that it was presumably set up for a German keyboard. From a terminal in Centos what do the below show? locale locale -a > > Hagen > -- Adrian Klaver adrian.klaver@gmail.com
Hi Hagen, > gpdemo | gpadmin | UTF8 | > that UTF8 ought to support the German characters I want. > Am I understanding you correctly? Yes, UTF-8 supports all the characters you'd want -- Wikipedia says it's about 109.000 characters from 93 scripts, so that's pretty everything you might need ( http://en.wikipedia.org/wiki/Unicode ). So as we have excluded the database storage and the psql connection, there seems to be only the terminal left as a suspect. Could you try using pgAdmin or any other non-console based tool to connect to the database in question? The plain text you first posted looked quite strange, repeating previous parts of the strings before the Umlauts -- usually, unsupported Umlauts show up rather as two characters, or cut off the rest of the word completely. My guess would be that the characters are correctly stored in the DB, and are just not displayed correctly within your terminal. But again, this is best verified when you connect directly to the DB. I don't know anything about terminals in CentOS, but have you tried setting the LANG variable? http://www.linuxreport.org/content/view/53/31/ Good luck :-) -hannes
As you indicated UTF-8 has the whole kitchen sink in it. I did trying using the German Keyboard Layout with a Centos texteditor and that works - I can produce the characters I want. Now I can also get the German characters to work in theCentos terminal but not in the psql command line client. Progress but still no joy. [gpadmin@gp-single-host ~]$ locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL= [gpadmin@gp-single-host ~]$ locale -a aa_DJ aa_DJ.iso88591 aa_DJ.utf8 aa_ER aa_ER@saaho aa_ER.utf8 aa_ER.utf8@saaho aa_ET aa_ET.utf8 af_ZA af_ZA.iso88591 af_ZA.utf8 am_ET am_ET.utf8 an_ES an_ES.iso885915 an_ES.utf8 ar_AE ar_AE.iso88596 ar_AE.utf8 ar_BH ar_BH.iso88596 ar_BH.utf8 ar_DZ ar_DZ.iso88596 ar_DZ.utf8 ar_EG ar_EG.iso88596 ar_EG.utf8 ar_IN ar_IN.utf8 ar_IQ ar_IQ.iso88596 ar_IQ.utf8 ar_JO ar_JO.iso88596 ar_JO.utf8 ar_KW ar_KW.iso88596 ar_KW.utf8 ar_LB ar_LB.iso88596 ar_LB.utf8 ar_LY ar_LY.iso88596 ar_LY.utf8 ar_MA ar_MA.iso88596 ar_MA.utf8 ar_OM ar_OM.iso88596 ar_OM.utf8 ar_QA ar_QA.iso88596 ar_QA.utf8 ar_SA ar_SA.iso88596 ar_SA.utf8 ar_SD ar_SD.iso88596 ar_SD.utf8 ar_SY ar_SY.iso88596 ar_SY.utf8 ar_TN ar_TN.iso88596 ar_TN.utf8 ar_YE ar_YE.iso88596 ar_YE.utf8 as_IN.utf8 az_AZ.utf8 be_BY be_BY.cp1251 be_BY@latin be_BY.utf8 be_BY.utf8@latin bg_BG bg_BG.cp1251 bg_BG.utf8 bn_BD bn_BD.utf8 bn_IN bn_IN.utf8 bokmal bokm br_FR br_FR@euro br_FR.iso88591 br_FR.iso885915@euro br_FR.utf8 bs_BA bs_BA.iso88592 bs_BA.utf8 byn_ER byn_ER.utf8 C ca_AD ca_AD.iso885915 ca_AD.utf8 ca_ES ca_ES@euro ca_ES.iso88591 ca_ES.iso885915@euro ca_ES.utf8 ca_FR ca_FR.iso885915 ca_FR.utf8 ca_IT ca_IT.iso885915 ca_IT.utf8 catalan croatian csb_PL csb_PL.utf8 cs_CZ cs_CZ.iso88592 cs_CZ.utf8 cy_GB cy_GB.iso885914 cy_GB.utf8 czech da_DK da_DK.iso88591 da_DK.iso885915 da_DK.utf8 danish dansk de_AT de_AT@euro de_AT.iso88591 de_AT.iso885915@euro de_AT.utf8 de_BE de_BE@euro de_BE.iso88591 de_BE.iso885915@euro de_BE.utf8 de_CH de_CH.iso88591 de_CH.utf8 de_DE de_DE@euro de_DE.iso88591 de_DE.iso885915@euro de_DE.utf8 de_LU de_LU@euro de_LU.iso88591 de_LU.iso885915@euro de_LU.utf8 deutsch dutch dz_BT dz_BT.utf8 eesti el_CY el_CY.iso88597 el_CY.utf8 el_GR el_GR.iso88597 el_GR.utf8 en_AU en_AU.iso88591 en_AU.utf8 en_BW en_BW.iso88591 en_BW.utf8 en_CA en_CA.iso88591 en_CA.utf8 en_DK en_DK.iso88591 en_DK.utf8 en_GB en_GB.iso88591 en_GB.iso885915 en_GB.utf8 en_HK en_HK.iso88591 en_HK.utf8 en_IE en_IE@euro en_IE.iso88591 en_IE.iso885915@euro en_IE.utf8 en_IN en_IN.utf8 en_NZ en_NZ.iso88591 en_NZ.utf8 en_PH en_PH.iso88591 en_PH.utf8 en_SG en_SG.iso88591 en_SG.utf8 en_US en_US.iso88591 en_US.iso885915 en_US.utf8 en_ZA en_ZA.iso88591 en_ZA.utf8 en_ZW en_ZW.iso88591 en_ZW.utf8 es_AR es_AR.iso88591 es_AR.utf8 es_BO es_BO.iso88591 es_BO.utf8 es_CL es_CL.iso88591 es_CL.utf8 es_CO es_CO.iso88591 es_CO.utf8 es_CR es_CR.iso88591 es_CR.utf8 es_DO es_DO.iso88591 es_DO.utf8 es_EC es_EC.iso88591 es_EC.utf8 es_ES es_ES@euro es_ES.iso88591 es_ES.iso885915@euro es_ES.utf8 es_GT es_GT.iso88591 es_GT.utf8 es_HN es_HN.iso88591 es_HN.utf8 es_MX es_MX.iso88591 es_MX.utf8 es_NI es_NI.iso88591 es_NI.utf8 es_PA es_PA.iso88591 es_PA.utf8 es_PE es_PE.iso88591 es_PE.utf8 es_PR es_PR.iso88591 es_PR.utf8 es_PY es_PY.iso88591 es_PY.utf8 es_SV es_SV.iso88591 es_SV.utf8 estonian es_US es_US.iso88591 es_US.utf8 es_UY es_UY.iso88591 es_UY.utf8 es_VE es_VE.iso88591 es_VE.utf8 et_EE et_EE.iso88591 et_EE.iso885915 et_EE.utf8 eu_ES eu_ES@euro eu_ES.iso88591 eu_ES.iso885915@euro eu_ES.utf8 fa_IR fa_IR.utf8 fi_FI fi_FI@euro fi_FI.iso88591 fi_FI.iso885915@euro fi_FI.utf8 finnish fo_FO fo_FO.iso88591 fo_FO.utf8 fran�ais fr_BE fr_BE@euro fr_BE.iso88591 fr_BE.iso885915@euro fr_BE.utf8 fr_CA fr_CA.iso88591 fr_CA.utf8 fr_CH fr_CH.iso88591 fr_CH.utf8 french fr_FR fr_FR@euro fr_FR.iso88591 fr_FR.iso885915@euro fr_FR.utf8 fr_LU fr_LU@euro fr_LU.iso88591 fr_LU.iso885915@euro fr_LU.utf8 fy_NL fy_NL.utf8 ga_IE ga_IE@euro ga_IE.iso88591 ga_IE.iso885915@euro ga_IE.utf8 galego galician gd_GB gd_GB.iso885915 gd_GB.utf8 german gez_ER gez_ER@abegede gez_ER.utf8 gez_ER.utf8@abegede gez_ET gez_ET@abegede gez_ET.utf8 gez_ET.utf8@abegede gl_ES gl_ES@euro gl_ES.iso88591 gl_ES.iso885915@euro gl_ES.utf8 greek gu_IN gu_IN.utf8 gv_GB gv_GB.iso88591 gv_GB.utf8 hebrew he_IL he_IL.iso88598 he_IL.utf8 hi_IN hi_IN.utf8 hr_HR hr_HR.iso88592 hr_HR.utf8 hrvatski hsb_DE hsb_DE.iso88592 hsb_DE.utf8 hu_HU hu_HU.iso88592 hu_HU.utf8 hungarian hy_AM hy_AM.armscii8 hy_AM.utf8 icelandic id_ID id_ID.iso88591 id_ID.utf8 is_IS is_IS.iso88591 is_IS.utf8 italian it_CH it_CH.iso88591 it_CH.utf8 it_IT it_IT@euro it_IT.iso88591 it_IT.iso885915@euro it_IT.utf8 iw_IL iw_IL.iso88598 iw_IL.utf8 ja_JP ja_JP.eucjp ja_JP.ujis ja_JP.utf8 japanese japanese.euc ka_GE ka_GE.georgianps ka_GE.utf8 kk_KZ kk_KZ.pt154 kk_KZ.utf8 kl_GL kl_GL.iso88591 kl_GL.utf8 km_KH km_KH.utf8 kn_IN kn_IN.utf8 ko_KR ko_KR.euckr ko_KR.utf8 korean korean.euc ku_TR ku_TR.iso88599 ku_TR.utf8 kw_GB kw_GB.iso88591 kw_GB.utf8 ky_KG ky_KG.utf8 lg_UG lg_UG.iso885910 lg_UG.utf8 lithuanian lo_LA lo_LA.utf8 lt_LT lt_LT.iso885913 lt_LT.utf8 lv_LV lv_LV.iso885913 lv_LV.utf8 mai_IN mai_IN.utf8 mg_MG mg_MG.iso885915 mg_MG.utf8 mi_NZ mi_NZ.iso885913 mi_NZ.utf8 mk_MK mk_MK.iso88595 mk_MK.utf8 ml_IN ml_IN.utf8 mn_MN mn_MN.utf8 mr_IN mr_IN.utf8 ms_MY ms_MY.iso88591 ms_MY.utf8 mt_MT mt_MT.iso88593 mt_MT.utf8 nb_NO nb_NO.iso88591 nb_NO.utf8 ne_NP ne_NP.utf8 nl_BE nl_BE@euro nl_BE.iso88591 nl_BE.iso885915@euro nl_BE.utf8 nl_NL nl_NL@euro nl_NL.iso88591 nl_NL.iso885915@euro nl_NL.utf8 nn_NO nn_NO.iso88591 nn_NO.utf8 no_NO no_NO.iso88591 no_NO.utf8 norwegian nr_ZA nr_ZA.utf8 nso_ZA nso_ZA.utf8 nynorsk oc_FR oc_FR.iso88591 oc_FR.utf8 om_ET om_ET.utf8 om_KE om_KE.iso88591 om_KE.utf8 or_IN or_IN.utf8 pa_IN pa_IN.utf8 pa_PK pa_PK.utf8 pl_PL pl_PL.iso88592 pl_PL.utf8 polish portuguese POSIX pt_BR pt_BR.iso88591 pt_BR.utf8 pt_PT pt_PT@euro pt_PT.iso88591 pt_PT.iso885915@euro pt_PT.utf8 romanian ro_RO ro_RO.iso88592 ro_RO.utf8 ru_RU ru_RU.iso88595 ru_RU.koi8r ru_RU.utf8 russian ru_UA ru_UA.koi8u ru_UA.utf8 rw_RW rw_RW.utf8 se_NO se_NO.utf8 sid_ET sid_ET.utf8 si_LK si_LK.utf8 sk_SK sk_SK.iso88592 sk_SK.utf8 slovak slovene slovenian sl_SI sl_SI.iso88592 sl_SI.utf8 so_DJ so_DJ.iso88591 so_DJ.utf8 so_ET so_ET.utf8 so_KE so_KE.iso88591 so_KE.utf8 so_SO so_SO.iso88591 so_SO.utf8 spanish sq_AL sq_AL.iso88591 sq_AL.utf8 sr_CS sr_CS.iso88595 sr_CS.utf8 sr_ME sr_ME.utf8 sr_RS sr_RS@latin sr_RS.utf8 sr_RS.utf8@latin ss_ZA ss_ZA.utf8 st_ZA st_ZA.iso88591 st_ZA.utf8 sv_FI sv_FI@euro sv_FI.iso88591 sv_FI.iso885915@euro sv_FI.utf8 sv_SE sv_SE.iso88591 sv_SE.iso885915 sv_SE.utf8 swedish ta_IN ta_IN.utf8 te_IN te_IN.utf8 tg_TJ tg_TJ.koi8t tg_TJ.utf8 thai th_TH th_TH.tis620 th_TH.utf8 ti_ER ti_ER.utf8 ti_ET ti_ET.utf8 tig_ER tig_ER.utf8 tl_PH tl_PH.iso88591 tl_PH.utf8 tn_ZA tn_ZA.utf8 tr_CY tr_CY.iso88599 tr_CY.utf8 tr_TR tr_TR.iso88599 tr_TR.utf8 ts_ZA ts_ZA.utf8 tt_RU.utf8 turkish uk_UA uk_UA.koi8u uk_UA.utf8 ur_PK ur_PK.utf8 uz_UZ uz_UZ@cyrillic uz_UZ.iso88591 uz_UZ.utf8@cyrillic ve_ZA ve_ZA.utf8 vi_VN vi_VN.tcvn vi_VN.utf8 wa_BE wa_BE@euro wa_BE.iso88591 wa_BE.iso885915@euro wa_BE.utf8 xh_ZA xh_ZA.iso88591 xh_ZA.utf8 yi_US yi_US.cp1255 yi_US.utf8 zh_CN zh_CN.gb18030 zh_CN.gb2312 zh_CN.gbk zh_CN.utf8 zh_HK zh_HK.big5hkscs zh_HK.utf8 zh_SG zh_SG.gb2312 zh_SG.gbk zh_SG.utf8 zh_TW zh_TW.big5 zh_TW.euctw zh_TW.utf8 zu_ZA zu_ZA.iso88591 zu_ZA.utf8 [gpadmin@gp-single-host ~]$ -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Monday, January 02, 2012 2:59 PM To: pgsql-general@postgresql.org Cc: Hagen Finley; 'Hannes Erven' Subject: Re: [GENERAL] Adding German Character Set to PostgresSQL On Monday, January 02, 2012 1:47:13 pm Hagen Finley wrote: > Hannes, > > The output of \l is: > > gpdemo=# \l > List of databases > Name | Owner | Encoding | Access privileges > ------------+---------+----------+--------------------- > acn | gpadmin | UTF8 | > gpdemo | gpadmin | UTF8 | > philosophy | gpadmin | UTF8 | > postgres | gpadmin | UTF8 | > template0 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin > template1 | gpadmin | UTF8 | =c/gpadmin : gpadmin=CTc/gpadmin > > It would be easy enough to create a new database with a different > encoding > - only one record in the one in question. However, it sounded as > though you don't believe that is the issue - that UTF8 ought to > support the German characters I want. Am I understanding you correctly? So now we have established where it is going to. Now to find out where it is coming from. You said the server is runningon Centos and that it was presumably set up for a German keyboard. From a terminal in Centos what do the below show? locale locale -a > > Hagen > -- Adrian Klaver adrian.klaver@gmail.com
On Monday, January 02, 2012 3:41:40 pm Hagen Finley wrote:
> As you indicated UTF-8 has the whole kitchen sink in it. I did trying using
> the German Keyboard Layout with a Centos text editor and that works - I
> can produce the characters I want. Now I can also get the German
> characters to work in the Centos terminal but not in the psql command line
> client. Progress but still no joy.
So you are using psql on the Centos machine?
I have the same locale as you, on my Linux machine, and using Pavels example I
get:
test(5432)aklaver=>create table x(a text);
CREATE TABLE
test(5432)aklaver=>insert into x values('ä,ß,ö');
INSERT 0 1
test(5432)aklaver=>SELECT * from x ;
a
-------
ä,ß,ö
(1 row)
What happens when you do the above on your machine?
>
--
Adrian Klaver
adrian.klaver@gmail.com
Yes I am running psql on Centos.
My psql client won't accept the German characters whether or not I attempt to type them or paste them.
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, January 02, 2012 5:05 PM
To: Hagen Finley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Adding German Character Set to PostgresSQL
On Monday, January 02, 2012 3:41:40 pm Hagen Finley wrote:
> As you indicated UTF-8 has the whole kitchen sink in it. I did trying
> using the German Keyboard Layout with a Centos text editor and that
> works - I can produce the characters I want. Now I can also get the
> German characters to work in the Centos terminal but not in the psql
> command line client. Progress but still no joy.
So you are using psql on the Centos machine?
I have the same locale as you, on my Linux machine, and using Pavels example I
get:
test(5432)aklaver=>create table x(a text); CREATE TABLE test(5432)aklaver=>insert into x values('ä,ß,ö'); INSERT 0 1
test(5432)aklaver=>SELECT* from x ;
a
-------
ä,ß,ö
(1 row)
What happens when you do the above on your machine?
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Monday, January 02, 2012 4:37:18 pm Hagen Finley wrote:
> Yes I am running psql on Centos.
>
> My psql client won't accept the German characters whether or not I attempt
> to type them or paste them.
>
So to be clear did you try?:
create table x(a text);
insert into x values('ä,ß,ö');
SELECT * from x ;
If so can you show the result?
Also maybe tail the log file to see if anything show up there?
--
Adrian Klaver
adrian.klaver@gmail.com
Yes but I couldn't input your second line - the ('ä,ß,ö') was not possible via the psql client - just got beeped when
Itried to type or paste those characters.
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, January 02, 2012 5:54 PM
To: Hagen Finley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Adding German Character Set to PostgresSQL
On Monday, January 02, 2012 4:37:18 pm Hagen Finley wrote:
> Yes I am running psql on Centos.
>
> My psql client won't accept the German characters whether or not I
> attempt to type them or paste them.
>
So to be clear did you try?:
create table x(a text);
insert into x values('ä,ß,ö');
SELECT * from x ;
If so can you show the result?
Also maybe tail the log file to see if anything show up there?
--
Adrian Klaver
adrian.klaver@gmail.com
On Monday, January 02, 2012 6:21:53 pm Hagen Finley wrote:
> Yes but I couldn't input your second line - the ('ä,ß,ö') was not possible
> via the psql client - just got beeped when I tried to type or paste those
> characters.
Hmmm.
Have you checked what client_encoding is set to in postgresql.conf and whether
that setting is uncommented?
Is the PGCLIENTENCODING environment variable set?
So what happens when you get the beep, is the character not allowed at all or is
changed?
Is there anything in the server logs?
--
Adrian Klaver
adrian.klaver@gmail.com
Hi Hagen,
all german umlaut characters works fine in postgres from my experience.
Seems you have encoding issues between windows tools/console/db-client. Use a utf8 capable client. Any java tool or pgadmin or similar are fine.
regards
Thomas
Am 02.01.2012 20:13, schrieb Hagen Finley:
all german umlaut characters works fine in postgres from my experience.
Seems you have encoding issues between windows tools/console/db-client. Use a utf8 capable client. Any java tool or pgadmin or similar are fine.
regards
Thomas
Am 02.01.2012 20:13, schrieb Hagen Finley:
Hi,
I am using psql (8.2.15) and I would like to input German characters (e.g. ä,ß,ö) into char fields I have in a database. I am having trouble getting the CENTOS Linux OS I am using to input German characters via a (apparently supported) German Keyboard Layout. However, that might be a separate matter. When I typed the German into Notepad in Windows and attempted to cut and paste the words into an INSERT statement, the characters do not persist:
Daß becomes DaDa and Heißt becomes HeiHeit which falls short of what I was hoping for.
I am wondering if I need to enable an international character set within Postgres before the German characters will input properly? If so, it’s not clear from the documentation I have attempted to find how one enables other characters sets within Postgres? Any suggestions will be much appreciated. Thank you.
Hagen Finley
Boulder, CO
* Hagen Finley:
> Yes but I couldn't input your second line - the ('ä,ß,ö') was not
> possible via the psql client - just got beeped when I tried to type or
> paste those characters.
If you start "cat" instead of "psql", can you enter those characters?
What about "python" or the shell itself? What terminal do you use?
This doesn't appear to be an issue with PostgreSQL as such, rather
something related to terminal configuration and perhaps readline.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
Am 02.01.2012 20:13, schrieb Hagen Finley: > I am using psql (8.2.15) and I would like to input German characters > (e.g. ä,ß,ö) into char fields I have in a database I see that you are using Outlook which leads me to assume you are running Windows as host for your transaction. Therefor you might are logged in into server with putty. Can you ensure you putty settings are correct so ä etc are get posted properly to database? Also what is bringing you to the points its not working correctly? Cheers, Frank
On 01/02/12 6:21 PM, Hagen Finley wrote:
> Yes but I couldn't input your second line - the ('ä,ß,ö') was not possible via the psql client - just got beeped
whenI tried to type or paste those characters.
the problem is, MS Windows and only MS Windows uses UTF16 instead of the
UTF8 that the entire rest of the world uses, so what looks right in
Notepad or other windows software, is in fact encoded wrong for any
non-MS UTF8 system. your SSH utility is supposed to be taking care of
the differences, and it generally works with VanDyke's SecureCRT but
I've had issues with PuTTY.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
Thank you all for your thoughtful suggestions. I just left for a 5 day East Coast business trip so I won't have a
chanceto tackle this gain until the weekend. For the record, I am running Centos as a VM on both a MAC OS 10.7 laptop
anda Windows 7 Workstation. In theory, I ought to be able use these differing platforms to test these suggestions more
rigorously.Thanks again!
Hagen Finley
hagen.finley@emc.com
Technical Business Consultant
EMC
303-886-1232
Sent from my iPad 2
On Jan 3, 2012, at 3:34, John R Pierce <pierce@hogranch.com> wrote:
> On 01/02/12 6:21 PM, Hagen Finley wrote:
>> Yes but I couldn't input your second line - the ('ä,ß,ö') was not possible via the psql client - just got beeped
whenI tried to type or paste those characters.
>
> the problem is, MS Windows and only MS Windows uses UTF16 instead of the UTF8 that the entire rest of the world uses,
sowhat looks right in Notepad or other windows software, is in fact encoded wrong for any non-MS UTF8 system. your SSH
utilityis supposed to be taking care of the differences, and it generally works with VanDyke's SecureCRT but I've had
issueswith PuTTY.
>
> --
> john r pierce N 37, W 122
> santa cruz ca mid-left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
On 2012-01-03, Frank Lanitz <frank@frank.uvena.de> wrote: > Am 02.01.2012 20:13, schrieb Hagen Finley: >> I am using psql (8.2.15) and I would like to input German characters >> (e.g. ä,ß,ö) into char fields I have in a database > > I see that you are using Outlook which leads me to assume you are > running Windows as host for your transaction. Therefor you might are > logged in into server with putty. Can you ensure you putty settings are > correct so ä etc are get posted properly to database? Also what is > bringing you to the points its not working correctly? last time I played with putty I discovered a setting that enabled UTF8 I didn't test it beyond verifying that slrn looked a lot nicer with UTF8 enabled, specifically I didn't test input, but I note that there is some UTF8 support but by default it is disabled. -- ⚂⚃ 100% natural
I wonder if the issue is in client encoding. How do these characters in various Windows codepages relate to UTF characters? Best Wishes, Chris Travers