Multibyte Support
Multibyte (MB) support is intended to allow PostgreSQL to handle multiple-byte character sets such as EUC (Extended UNIX Code), Unicode and Mule internal code. With multibyte support 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 PostgreSQL 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.
Multibyte support also fixes some problems concerning 8-bit single byte character sets, including ISO8859.
Enabling Multibyte Support
Multibyte support is enabled by default in Red Hat Database.
If you have a non-default system and now want to enable multibyte support, 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 3-1. Character Set Encodings
| Encoding | Description |
|---|---|
| SQL_ASCII | ASCII |
| EUC_JP | Japanese EUC |
| EUC_CN | Chinese EUC |
| EUC_KR | Korean EUC |
| EUC_TW | Taiwan EUC |
| UNICODE | Unicode (UTF-8) |
| MULE_INTERNAL | Mule internal code |
| LATIN1 | ISO 8859-1 ECMA-94 Latin Alphabet No.1 |
| LATIN2 | ISO 8859-2 ECMA-94 Latin Alphabet No.2 |
| LATIN3 | ISO 8859-3 ECMA-94 Latin Alphabet No.3 |
| LATIN4 | ISO 8859-4 ECMA-94 Latin Alphabet No.4 |
| LATIN5 | ISO 8859-9 ECMA-128 Latin Alphabet No.5 |
| LATIN6 | ISO 8859-10 ECMA-144 Latin Alphabet No.6 |
| LATIN7 | ISO 8859-13 Latin Alphabet No.7 |
| LATIN8 | ISO 8859-14 Latin Alphabet No.8 |
| LATIN9 | ISO 8859-15 Latin Alphabet No.9 |
| LATIN10 | ISO 8859-16 ASRO SR 14111 Latin Alphabet No.10 |
| ISO-8859-5 | ECMA-113 Latin/Cyrillic |
| ISO-8859-6 | ECMA-114 Latin/Arabic |
| ISO-8859-7 | ECMA-118 Latin/Greek |
| ISO-8859-8 | ECMA-121 Latin/Hebrew |
| KOI8 | KOI8-R(U) |
| WIN | Windows CP1251 |
| ALT | Windows CP866 |
 | Before PostgreSQL7.2, LATIN5 mistakenly meant ISO 8859-5. From 7.2 on, LATIN5 means ISO 8859-9. If you have a LATIN5 database created on 7.1 or earlier and want to migrate to 7.2 (or later), you should be very careful about this change. Not all APIs supports all the encodings listed above. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, or LATIN10. |
Here is an example of configuring PostgreSQL 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.
Setting the Encoding
initdb defines the default encoding for a PostgreSQL installation. For example:
$ initdb -E EUC_JP |
You can create a database with a different encoding:
$ createdb -E EUC_KR korean |
CREATE DATABASE korean WITH ENCODING = 'EUC_KR'; |
$ 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) |
Automatic Encoding Translation Between Server and Client
PostgreSQL supports an automatic encoding translation between server and client for some encodings. The available combinations are listed in Table 3-2.
Table 3-2. Client/Server Character Set Encodings
| Server Encoding | Available Client Encodings |
|---|---|
| SQL_ASCII | SQL_ASCII, UNICODE, MULE_INTERNAL |
| EUC_JP | EUC_JP, SJIS, UNICODE, MULE_INTERNAL |
| EUC_TW | EUC_TW, BIG5, UNICODE, MULE_INTERNAL |
| LATIN1 | LATIN1, UNICODE MULE_INTERNAL |
| LATIN2 | LATIN2, WIN1250, UNICODE, MULE_INTERNAL |
| LATIN3 | LATIN3, UNICODE MULE_INTERNAL |
| LATIN4 | LATIN4, UNICODE MULE_INTERNAL |
| LATIN5 | LATIN5, UNICODE MULE_INTERNAL |
| LATIN6 | LATIN6, UNICODE MULE_INTERNAL |
| LATIN7 | LATIN7, UNICODE MULE_INTERNAL |
| LATIN8 | LATIN8, UNICODE MULE_INTERNAL |
| LATIN9 | LATIN9, UNICODE MULE_INTERNAL |
| LATIN10 | LATIN10, UNICODE MULE_INTERNAL |
| ISO_8859_5 | ISO_8859_5, UNICODE |
| ISO_8859_6 | ISO_8859_6, UNICODE |
| ISO_8859_7 | ISO_8859_7, UNICODE |
| ISO_8859_8 | ISO_8859_8, UNICODE |
| ISO_8859_9 | ISO_8859_9, WIN, ALT, KOI8R, UNICODE, MULE_INTERNAL |
| UNICODE | EUC_JP, SJIS, EUC_KR, EUC_CN, EUC_TW, BIG5, LATIN1 to LATIN10, ISO_8859_5, ISO_8859_6, ISO_8859_7, ISO_8859_8, WIN, ALT, KOI8 |
| MULE_INTERNAL | EUC_JP, SJIS, EUC_KR, EUC_CN, EUC_TW, BIG5, LATIN1 to LATIN5, WIN, ALT, WIN1250 |
| KOI8 | ISO_8859_9, WIN, ALT, KOI8, UNICODE, MULE_INTERNAL |
| WIN | ISO_8859_9, WIN, ALT, KOI8, UNICODE, MULE_INTERNAL |
| ALT | ISO_8859_9, WIN, ALT, KOI8, UNICODE, MULE_INTERNAL |
To enable the automatic encoding translation, you have to tell PostgreSQL the encoding you would like to use in the frontend. There are several ways to accomplish this:
Using the \encoding command in psql. \encoding allows you to change frontend encoding on the fly. For example, to change the encoding to SJIS, type:
\encoding SJIS
Using libpq functions. \encoding actually calls PQsetClientEncoding() for its purpose.
where conn is a connection to the backend, and encoding is an encoding you want to use. If it successfully sets the encoding, it returns 0, otherwise -1. The current encoding for this connection can be shown by using:int PQsetClientEncoding(PGconn *conn, const char *encoding)
Note that it returns the "encoding id," not the encoding symbol string such as EUC_JP. To convert an encoding id to an encoding symbol, you can use:int PQclientEncoding(const PGconn *conn)
char *pg_encoding_to_char(int encoding_id)
Using SET CLIENT_ENCODING TO. You can set frontend encoding with this SQL command:
Also you can use SQL92 syntax "SET NAMES" for this purpose:SET CLIENT_ENCODING TO 'encoding';
To query the current frontend encoding:SET NAMES 'encoding';
To return to the default encoding:SHOW CLIENT_ENCODING;
RESET CLIENT_ENCODING;
Using PGCLIENTENCODING. If environment variable PGCLIENTENCODING is defined in the client's environment, that client encoding is automatically selected when a backend connection is made. This can subsequently be overridden using any of the other methods mentioned above.
About Unicode
An automatic encoding translation between Unicode and other encodings has been supported since PostgreSQL 7.1. For 7.1 it was 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.
For 7.2, --enable-unicode-conversion is not necessary. The Unicode conversion functionality is automatically enabled if --enable-multibyte is specified.
When 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) |