Thread: Adding German Character Set to PostgresSQL

Adding German Character Set to PostgresSQL

From
"Hagen Finley"
Date:

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

Re: Adding German Character Set to PostgresSQL

From
Adrian Klaver
Date:
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

Re: Adding German Character Set to PostgresSQL

From
"Hagen Finley"
Date:

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

adrian.klaver@gmail.com

Re: Adding German Character Set to PostgresSQL

From
Pavel Stehule
Date:
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

Re: Adding German Character Set to PostgresSQL

From
Hannes Erven
Date:
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

Re: Adding German Character Set to PostgresSQL

From
Adrian Klaver
Date:
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 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?

It should.

>
>
>
> Hagen
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Adding German Character Set to PostgresSQL

From
"Hagen Finley"
Date:
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



Re: Adding German Character Set to PostgresSQL

From
Adrian Klaver
Date:
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

Re: Adding German Character Set to PostgresSQL

From
Hannes Erven
Date:
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

Re: Adding German Character Set to PostgresSQL

From
"Hagen Finley"
Date:
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



Re: Adding German Character Set to PostgresSQL

From
Adrian Klaver
Date:
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

Re: Adding German Character Set to PostgresSQL

From
"Hagen Finley"
Date:
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



Re: Adding German Character Set to PostgresSQL

From
Adrian Klaver
Date:
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

Re: Adding German Character Set to PostgresSQL

From
"Hagen Finley"
Date:
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



Re: Adding German Character Set to PostgresSQL

From
Adrian Klaver
Date:
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

Re: Adding German Character Set to PostgresSQL

From
Thomas Markus
Date:
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:

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


Re: Adding German Character Set to PostgresSQL

From
Florian Weimer
Date:
* 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

Re: Adding German Character Set to PostgresSQL

From
Frank Lanitz
Date:
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

Re: Adding German Character Set to PostgresSQL

From
John R Pierce
Date:
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



Re: Adding German Character Set to PostgresSQL

From
Hagen Finley
Date:
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

Re: Adding German Character Set to PostgresSQL

From
Jasen Betts
Date:
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

Re: Adding German Character Set to PostgresSQL

From
Chris Travers
Date:
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