Character Set Conversion Between Server and Client in Greenplum

Greenplum Database supports automatic character set conversion between server and client for certain character set combinations. The conversion information is stored in the master pg_conversion system catalog table. Greenplum Database comes with some predefined conversions or you can create a new conversion using the SQL command CREATE CONVERSION.

To enable automatic character set conversion, you have to tell Greenplum Database the character set (encoding) you would like to use in the client. There are several ways to accomplish this:

1. Using the \encoding command in psql, which allows you to change client encoding on the fly.

2. Using SET client_encoding TO. Setting the client encoding can be done with this SQL command:
SET CLIENT_ENCODING TO 'value';

To query the current client encoding:
SHOW client_encoding;

To return to the default encoding:
RESET client_encoding;

3. Using the PGCLIENTENCODING environment variable. When PGCLIENTENCODING is defined in the client’s environment, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

4. Setting the configuration parameter client_encoding. If client_encoding is set in the master postgresql.conf file, that client encoding is automatically selected when a connection to Greenplum Database is made. (This can subsequently be overridden using any of the other methods mentioned above.)

- If the conversion of a particular character is not possible — suppose you chose EUC_JP for the server and LATIN1 for the client, then some Japanese characters do not have a representation in LATIN1 — then an error is reported.

- If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server’s character set. The use of SQL_ASCII is unwise unless you are working with all-ASCII data. SQL_ASCII is not supported as a server encoding.


 Server Character Set Available Client Character Sets
 BIG5 Not supported as a server encoding
 EUC_CN EUC_CN, MULE_INTERNAL, UTF8
 EUC_JP EUC_JP, MULE_INTERNAL, SJIS, UTF8
 EUC_KR EUC_KR, MULE_INTERNAL, UTF8
 EUC_TW EUC_TW, BIG5, MULE_INTERNAL, UTF8
 GB18030 Not supported as a server encoding
 GBK Not supported as a server encoding
 ISO_8859_5 ISO_8859_5, KOI8, MULE_INTERNAL, UTF8, WIN866, WIN1251
 ISO_8859_6 ISO_8859_6, UTF8
 ISO_8859_7 ISO_8859_7, UTF8
 ISO_8859_8 ISO_8859_8, UTF8
 JOHAB JOHAB, UTF8
 KOI8 KOI8, ISO_8859_5, MULE_INTERNAL, UTF8, WIN866, WIN1251
 LATIN1 LATIN1, MULE_INTERNAL, UTF8
 LATIN2 LATIN2, MULE_INTERNAL, UTF8, WIN1250
 LATIN3 LATIN3, MULE_INTERNAL, UTF8
 LATIN4 LATIN4, MULE_INTERNAL, UTF8
 LATIN5 LATIN5, UTF8
 LATIN6 LATIN6, UTF8
 LATIN7 LATIN7, UTF8
 LATIN8 LATIN8, UTF8
 LATIN9 LATIN9, UTF8
 LATIN10 LATIN10, UTF8
 MULE_INTERNAL MULE_INTERNAL, BIG5, EUC_CN, EUC_JP, EUC_KR, EUC_TW, ISO_8859_5, KOI8, LATIN1 to LATIN4, SJIS, WIN866, WIN1250, WIN1251
 SJIS Not supported as a server encoding
 SQL_ASCIINot supported as a server encoding
 UHC Not supported as a server encoding
 UTF8 All supported encodings
 WIN866 WIN866
 ISO_8859_5 KOI8, MULE_INTERNAL, UTF8, WIN1251
 WIN874 WIN874, UTF8
 WIN1250 WIN1250, LATIN2, MULE_INTERNAL, UTF8
 WIN1251 WIN1251, ISO_8859_5, KOI8, MULE_INTERNAL, UTF8, WIN866
 WIN1252 WIN1252, UTF8
 WIN1253 WIN1253, UTF8
 WIN1254 WIN1254, UTF8
 WIN1255 WIN1255, UTF8
 WIN1256 WIN1256, UTF8
 WIN1257 WIN1257, UTF8
 WIN1258 WIN1258, UTF8


Comments