How to convert string to unicode using PostgreSQL?

Question

Here I want to convert my string to unicode. I am using PostgreSQL 9.3 version. In SQL Server its much more easy:

Example:

   sql = N'select * from tabletest'; /* For nvarchar/nchar/ntext */

OR

   sql = U'select * from tabletest'; /* For varchar/char/text */

Question: How can I do the above conversion in PostgreSQL?

Answer 1

PostgreSQL databases have a native character type, the "server encoding". It is usually utf-8.

All text is in this encoding. Mixed encoding text is not supported, except if stored as bytea (i.e. as opaque byte sequences).

You can't store "unicode" or "non-unicode" strings, and PostgreSQL has no concept of "varchar" vs "nvarchar". With utf-8, characters that fall in the 7-bit ASCII range (and some others) are stored as a single byte, and wider chars require more storage, so it's just automatic. utf-8 requires more storage than ucs-2 or utf-16 for text that is all "wide" characters, but less for text that's a mixture.

PostgreSQL automatically converts to/from the client's text encoding, using the client_encoding setting. There is no need to convert explicitly.

If your client is "Unicode" (which Microsoft products tend to say when they mean UCS-2 or UTF-16), then most client drivers take care of any utf-8 <--> utf-16 conversion for you.

So you should not need to care, so long as your client does I/O with correct charset options and sets a correct client_encoding that matches the data its actually sends on the wire. (This is automatic with most client drivers like PgJDBC, nPgSQL, or the Unicode psqlODBC driver).

See:

• character set support