Multilingual Database Support With Unicode UCS-2
If you choose to store text as UCS-2 you will use the NCHAR, NVARCHAR, and NLONGVARCHAR relational types. This has no effect on your ability to also store other text in the CHAR family of relational types. You also need to consider such aspects as the Unicode support for the operating system on which your application runs, the string manipulation libraries available to your application, the PSQL access method(s) your application uses, any columns that may need a different data type, and so forth.
When to Use Unicode UCS-2
Unicode UCS-2 is a good option for the following situations:
•
Your application supports Asian character data. With UCS-2, all characters are stored as two bytes which provides more compact storage than UTF-8 for Asian character data.
•
Your application will not be globalizing all of the storage and so will have wide character columns together with legacy byte string columns.
•
Your application needs better compatibility for wide character data with Java, ADO.NET, or wide character clients. Such applications use UCS-2 for application strings.
Unicode UCS-2 Support in PSQL
Wide character storage is a separate type from byte-string storage and may be used alongside byte-string storage.
For wide character strings, PSQL provides the relational data types NCHAR, NVARCHAR, and NLONGVARCHAR, and the Btrieve data types WSTRING and WZSTRING. All data inserted by your application for NCHAR, NVARCHAR, NLONGVARCHAR, WSTRING and WZSTRING data types is interpreted as wide character strings.
PSQL supports Unicode characters in NCHAR literals in SQL query text. Text data in CHAR literals is translated to the database code page. Keep in mind that database code pages other than UTF-8 cannot handle most Unicode characters.
Collation and Sorting with Unicode UCS-2
PSQL supports only code point order for collation and sorting with UCS-2 storage.
Access Methods for Unicode UCS-2 Support
Using NCHAR storage with the SQL access methods requires that applications specify the appropriate NCHAR SQL types to the access method. Use of CHAR types can cause conversion from NCHAR to CHAR and loss of data if the database code page is not UTF-8.
•
ODBC applications should use the SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR SQL types for parameters.
•
Windows ODBC applications should be using wide character strings, identified to ODBC as SQL_C_WCHAR. Also, Windows ODBC applications should use the PSQL Unicode ODBC driver. Use of the legacy ANSI ODBC driver will result in loss of data because the Windows Driver Manager will convert all wide character strings to byte string.
•
Linux and OS X ODBC applications should use a locale with a UTF-8 encoding and use UTF-8 in application byte strings (SQL_C_CHAR) to provide data for NCHAR values (SQL_WCHAR).
•
JDBC applications should set string parameters to type NVARCHAR or use the ‘N’ methods such as setNString() to send string data as an NCHAR SQL type. Also, set pvtranslate=auto in the connection string and set the database code page. Otherwise, JDBC will remain backward compatible and use the declared or default byte-string encoding for string data. See Connection String Overview in JDBC Driver Guide.
•
ADO.NET applications need to specify pvtranslate=auto in the connection string to the database engine. See Adding Connections in Data Provider for .NET Guide.
In all cases, use NCHAR literals in SQL text.
Migrating an Existing Database to Unicode UCS-2
The tasks for migrating an existing database to support a UCS-2 application involve converting byte strings to wide character strings. The extent of the conversion depends on your application. ALTER TABLE can be used to convert columns provided that the database code page is set to match the encoding for existing CHAR columns. It is only necessary to convert columns that will store globalized data. Columns that will never store characters outside of the character set of the database code page can remain as CHAR types.
If your application uses the ANSI ODBC driver on Windows, you need to convert your application to wide character data and use the PSQL Unicode ODBC driver.