ASCII 152 Character (“ÿ”) PostgreSQL upper layer function

"ada"
"john"
"mole" 
"ÿÿÿ"

select thecol, upper(thecol), upper(thecol) is null, convert_to(thecol,'UTF8'), current_setting( 'server_encoding') from thetable where ...

"Apps";"APPS";f;"Apps";"UTF8" 
"All";"ALL";f;"All";"UTF8"
"Test";"TEST";f;"Test";"UTF8"
"ÿÿÿ"; "";f;"037703770377";"UTF8"

"lc_collate";"Swedish_Sweden.1252";"Shows the collation order locale."
"lc_ctype";"Swedish_Sweden.1252";"Shows the character classification and case conversion locale."
"lc_messages ";"Swedish_Sweden.1252";"Sets the language in which messages are displayed."
"lc_monetary";"Swedish_Sweden.1252";"Sets the locale for formatting monetary amounts."
"lc_numeric" ;"Swedish_Sweden.1252";"Sets the locale for formatting numbers."

"template1";10 ;6;"Swedish_Sweden.1252";"Swedish_Sweden.1252";t;t;-1;12130;668;1‌​;1663;"{=c/postg res,postgres=CTc/postgres}" 
"template0";10;6;"Swedish_Sweden.1252";"Swedish_Sweden.1252";t;f;-1;12130;668;1‌​;1663;" {=c/postgres,postgres=CTc/postgres}"
"postgres";10;6;"Swedish_Sweden.1252";"Swedish_Sweden.1252";f;t;-1;12130;668;1; ‌​1663;""

CREATE DATABASE postgres
 WITH OWNER = postgres
 ENCODING ='UTF8'
 TABLESPACE = pg_default
 LC_COLLATE ='Swedish_Sweden.1252'
 LC_CTYPE ='Swedish_Sweden.1252'
 CONNECTION LIMIT = -1;< /pre> 

SELECT upper(convert_to(thecol,'UTF8')) ...

On the Windows 7 platform, use PostgreSQL version 9.3.9, use PgAdmin as the client, and select the upper part on the column containing the example The result of "ÿÿÿ", returns null. If three values ​​are stored, for example,

"ada"
"john"
"mole "
"ÿÿÿ"

Except for the line containing "ÿÿÿ", they are all returned in uppercase; this line
nothing is returned, null …

The database encoding scheme is UTF8 / UNICODE. Set "client_encoding" to have the same value UNICODE.

This is a setting problem in the database, an operating system problem or an error
In the database? Are there some recommended solutions?

Result:

select thecol, upper(thecol), upper(thecol) is null, convert_to(thecol,'UTF8'), current_setting( 'server_encoding') from thetable where ...

Yes:

"Apps";"APPS";f;"Apps";"UTF8" 
"All";"ALL";f;"All";"UTF8"
"Test";"TEST";f;"Test";"UTF8"
"ÿÿÿ"; "";f;"037703770377";"UTF8"

The lc_ part of pg_settings is:

"lc_collate";"Swedish_Sweden.1252";"Shows the collation order locale."
"lc_ctype";"Swedish_Sweden.1252";"Shows the character classification and case conversion locale."
"lc_messages ";"Swedish_Sweden.1252";"Sets the language in which messages are displayed."
"lc_monetary";"Swedish_Sweden.1252";"Sets the locale for formatting monetary amounts."
"lc_numeric" ;"Swedish_Sweden.1252";"Sets the locale for formatting numbers."

The output of select * from pg_database is:

"template1";10 ;6;"Swedish_Sweden.1252";"Swedish_Sweden.1252";t;t;-1;12130;668;1‌​;1663;"{=c/postgres,postgres =CTc/postgres}" 
"template0";10;6;"Swedish_Sweden.1252";"Swedish_Sweden.1252";t;f;-1;12130;668;1‌​;1663;"{=c /postgres,postgres=CTc/postgres}"
"postgres";10;6;"Swedish_Sweden.1252";"Swedish_Sweden.1252";f;t;-1;12130;668;1;‌​1663 ;""

For version 9.4.4, the actual create database statement is:

CREATE DATABASE postgres
 WITH OWNER = postgres
 ENCODING ='UTF8'
 TABLESPACE = pg_default
 LC_COLLATE ='Swedish_Sweden.1252'
 LC_CTYPE ='Swedish_Sweden.1252'
 CONNECTION LIMIT = -1;

My guess is that the upper-level function uses the LC_CTYPE setting of the database. The capital letter of LATIN SMALL LETTER Y with DIAERESIS (U 00FF) is LATIN CAPITAL LETTER Y WITH DIAERESIS'( U 0178), it is not part of the Windows 1252 code page.

If the string is first converted to Unicode format, the upper function may work as expected:

< /p>

SELECT upper(convert_to(thecol,'UTF8')) ...

You should use different values ​​for LC_CTYPE and LC_COLLATE. On Linux, you will use sv_SE.UTF- 8.

However, I think this is a bug in Postgres. If the uppercase version cannot be represented in the target character set, it is better to keep ÿ.