[GENERAL] citext data type does not work with JDBC PreparedStatement?

[Anton Moiseev]

Hi,

I wanted to have case-insensitive user names in my db and found that citext
postgresql data type (
http://www.postgresql.org/docs/8.4/interactive/citext.html) is exactly what
I need.

So I have added to my db and it seemed to work fine when query db from
command line interface, but when I run it from java prepared statement,
things do not work as expected.

For example, I have user name 'Leon' stored in the db and want to get
password for him.

If I execute query in sql console:
SELECT password FROM users WHERE name = 'leon';

it returns password ok.

But if I do the query from java in this way:

        final String query = "SELECT password FROM users WHERE name = ?";
        final PreparedStatement stmt = dbConnection.prepareStatement(query);
        stmt.setString(1, "leon");
        final ResultSet resultSet = stmt.executeQuery();
        if(resultSet.next()) {
            System.out.println("password is:" + resultSet.getString(1));
        } else {
            System.out.println("password not found");
        }


password won't be found. If I change parameter substitution like that:

        final String query = "SELECT password FROM users WHERE name =
'leon'";
        final PreparedStatement stmt = dbConnection.prepareStatement(query);
        final ResultSet resultSet = stmt.executeQuery();
        if(resultSet.next()) {
            System.out.println("password is:" + resultSet.getString(1));
        } else {
            System.out.println("password not found");
        }

the password would be again returned.

Any ideas why this can happen and how to fix this?

thank's
Anton