Cannot select Unicode data from PostgreSQL with LIKE

Question

I have a PostgreSQL database with some Unicode values. For example "vaishali" in Marathi. I want to fire a query SELECT * FROM table WHERE name LIKE vaishali (I type "vaishali" in Marathi, so I first convert to unicode in my prog). But it matches nothing. Why?

Answer 1

There are two possible causes for this problem:

1. The JDBC driver is not instructed to use UTF-8. This would actually be a bug, because it's supposed to automatically pickup the correct character encoding from the DB metadata. To my experience, the PostgreSQL JDBC drivers have always done a good job in this.

2. The SQL query is actually wrongly constructed/formulated. The one which you've posted is already syntactically invalid. Please post the actual code and query. Also, you seem to insist that it only happens with a LIKE query. How about a normal WHERE name = 'foo' query?