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.