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?
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:
\lat the psql prompt or queryshow server_encoding;.