5.2. Multibyte Support

Author: Tatsuo Ishii (), last updated 2000-03-22. Check Tatsuo's web site for more information.

Multibyte (MB) support is intended to allow Postgres to handle multiple-byte character sets such as EUC (Extended Unix Code), Unicode and Mule internal code. With MB enabled you can use multi-byte character sets in regular expressions (regexp), LIKE, and some other functions. The default encoding system is selected while initializing your Postgres installation using initdb. Note that this can be overridden when you create a database using createdb or by using the SQL command CREATE DATABASE. So you can have multiple databases each with a different encoding system.

MB also fixes some problems concerning 8-bit single byte character sets including ISO8859. (I would not say all problems have been fixed. I just confirmed that the regression test ran fine and a few French characters could be used with the patch. Please let me know if you find any problem while using 8-bit characters.)

5.2.1. Enabling MB

Run configure with the multibyte option:

% ./configure --enable-multibyte[=encoding_system]
     
where encoding_system can be one of the values in the following table:

Table 5-1. Postgres Character Set Encodings

EncodingDescription
SQL_ASCIIASCII
EUC_JPJapanese EUC
EUC_CNChinese EUC
EUC_KRKorean EUC
EUC_TWTaiwan EUC
UNICODEUnicode(UTF-8)
MULE_INTERNALMule internal
LATIN1ISO 8859-1 English and some European languages
LATIN2ISO 8859-2 English and some European languages
LATIN3ISO 8859-3 English and some European languages
LATIN4ISO 8859-4 English and some European languages
LATIN5ISO 8859-5 English and some European languages
KOI8KOI8-R(U)
WINWindows CP1251
ALTWindows CP866

Here is an example of configuring Postgres to use a Japanese encoding by default:

% ./configure --enable-multibyte=EUC_JP
     

If the encoding system is omitted (./configure --enable-multibyte), SQL_ASCII is assumed.

5.2.2. Setting the Encoding

initdb defines the default encoding for a Postgres installation. For example:

% initdb -E EUC_JP
     
sets the default encoding to EUC_JP (Extended Unix Code for Japanese). Note that you can use "--encoding" instead of "-E" if you prefer to type longer option strings. If no -E or --encoding option is given, the encoding specified at configure time is used.

You can create a database with a different encoding:

% createdb -E EUC_KR korean
     
will create a database named "korean" with EUC_KR encoding. Another way to accomplish this is to use a SQL command:
CREATE DATABASE korean WITH ENCODING = 'EUC_KR';
     
The encoding for a database is represented as an encoding column in the pg_database system catalog. You can see that by using -l or \l of psql command.
$ psql -l
            List of databases
   Database    |  Owner  |   Encoding    
---------------+---------+---------------
 euc_cn        | t-ishii | EUC_CN
 euc_jp        | t-ishii | EUC_JP
 euc_kr        | t-ishii | EUC_KR
 euc_tw        | t-ishii | EUC_TW
 mule_internal | t-ishii | MULE_INTERNAL
 regression    | t-ishii | SQL_ASCII
 template1     | t-ishii | EUC_JP
 test          | t-ishii | EUC_JP
 unicode       | t-ishii | UNICODE
(9 rows)
     

5.2.3. Automatic encoding translation between backend and frontend

Postgres supports an automatic encoding translation between backend and frontend for some encodings.

Table 5-2. Postgres Client/Server Character Set Encodings

Server EncodingAvailable Client Encodings
EUC_JPEUC_JP, SJIS
EUC_TWEUC_TW, BIG5
LATIN2LATIN2, WIN1250
LATIN5LATIN5, WIN, ALT
MULE_INTERNALEUC_JP, SJIS, EUC_KR, EUC_CN, EUC_TW, BIG5, LATIN1 to LATIN5, WIN, ALT, WIN1250

To enable the automatic encoding translation, you have to tell Postgres the encoding you would like to use in frontend. There are several ways to accomplish this.

5.2.4. About Unicode

An automatic encoding translation between Unicode and other encodings has been supported since PostgreSQL 7.1. Because this requires huge conversion tables, it's not enabled by default. To enable this feature, run configure with the --enable-unicode-conversion option. Note that this requires the --enable-multibyte option also.

5.2.5. What happens if the translation is not possible?

Suppose you choose EUC_JP for the backend, LATIN1 for the frontend, then some Japanese characters could not be translated into LATIN1. In this case, a letter that cannot be represented in the LATIN1 character set would be transformed as:

(HEXA DECIMAL)
     

5.2.6. References

These are good sources to start learning about various kinds of encoding systems.

5.2.7. History

Dec 7, 2000
	* An automatic encoding translation between Unicode and other
	  encodings are implemented
	* Changes above will appear in 7.1

May 20, 2000
	* SJIS UDC (NEC selection IBM kanji) support contributed
	  by Eiji Tokuya
	* Changes above will appear in 7.0.1

Mar 22, 2000
	* Add new libpq functions PQsetClientEncoding, PQclientEncoding
	* ./configure --with-mb=EUC_JP
	  now deprecated. use 
	  ./configure --enable-multibyte=EUC_JP
	  instead
  	* Add SQL_ASCII regression test case
	* Add SJIS User Defined Character (UDC) support
	* All of above will appear in 7.0

July 11, 1999
	* Add support for WIN1250 (Windows Czech) as a client encoding
	  (contributed by Pavel Behal)
	* fix some compiler warnings (contributed by Tomoaki Nishiyama)

Mar 23, 1999
	* Add support for KOI8(KOI8-R), WIN(CP1251), ALT(CP866)
	  (thanks Oleg Broytmann for testing)
	* Fix problem with MB and locale

Jan 26, 1999
	* Add support for Big5 for fronend encoding
	  (you need to create a database with EUC_TW to use Big5)
	* Add regression test case for EUC_TW
	  (contributed by Jonah Kuo )

Dec 15, 1998
	* Bugs related to SQL_ASCII support fixed

Nov 5, 1998
	* 6.4 release. In this version, pg_database has "encoding"
	  column that represents the database encoding

Jul 22, 1998
	* determine encoding at initdb/createdb rather than compile time
	* support for PGCLIENTENCODING when issuing COPY command
	* support for SQL92 syntax "SET NAMES"
	* support for LATIN2-5
	* add UNICODE regression test case
	* new test suite for MB
	* clean up source files

Jun 5, 1998
	* add support for the encoding translation between the backend
	  and the frontend
	* new command SET CLIENT_ENCODING etc. added
	* add support for LATIN1 character set
	* enhance 8 bit cleaness

April 21, 1998 some enhancements/fixes
	* character_length(), position(), substring() are now aware of 
	  multi-byte characters
	* add octet_length()
	* add --with-mb option to configure
	* new regression tests for EUC_KR
  	  (contributed by Soonmyung Hong )
	* add some test cases to the EUC_JP regression test
	* fix problem in regress/regress.sh in case of System V
	* fix toupper(), tolower() to handle 8bit chars

Mar 25, 1998 MB PL2 is incorporated into PostgreSQL 6.3.1

Mar 10, 1998 PL2 released
	* add regression test for EUC_JP, EUC_CN and MULE_INTERNAL
	* add an English document (this file)
	* fix problems concerning 8-bit single byte characters

Mar 1, 1998 PL1 released
     

5.2.8. WIN1250 on Windows/ODBC

The WIN1250 character set on Windows client platforms can be used with Postgres with locale support enabled.

The following should be kept in mind:

When running, it is important to remember the following:

WIN1250 on Windows/ODBC

  1. Compile Postgres with locale enabled and the multibyte encoding set to LATIN2.

  2. Set up your installation. Do not forget to create locale variables in your profile (environment). For example (this may not be correct for your environment):

    LC_ALL=cs_CZ.ISO8859-2
    LC_COLLATE=cs_CZ.ISO8859-2
    LC_CTYPE=cs_CZ.ISO8859-2
    LC_MONETARY=cs_CZ.ISO8859-2
    LC_NUMERIC=cs_CZ.ISO8859-2
    LC_TIME=cs_CZ.ISO8859-2
           

  3. You have to start the postmaster with locales set!

  4. Try it with Czech language, and have it sort on a query.

  5. Install ODBC driver for PgSQL on your M$ Windows machine.

  6. Setup properly your data source. Include this line in your ODBC configuration dialog in the field Connect Settings:

    SET CLIENT_ENCODING = 'WIN1250';
           

  7. Now try it again, but in Windows with ODBC.