Issue : Citext type not supported by PG JDBC driver.

Hello PGSQL Community,

 

I have been trying to explore and exploit the "citext" data-type which provides for case-insensitive behavior. This is really a very cool feature but currently I'm facing issues trying to make this work in a case-insensitive manner using plain JDBC queries. This defies the entire purpose of the column type.

 

Environment  :
PostgreSQL server : 9.4.1211
Pgsql JDBC version : JDBC41 Postgresql Driver

 

 

 I have a table where column : name is citext type. There is a row with name column value as "Postgres". If I now write a plain JDBC query to fetch all rows where name = "postgres", it returns no result.  Ideally, name being a citext column type, the query should have returned one row as result.

 

 

On analyzing further, I found that the field values for the column values fetched in the PgResultSetMetaData has "unknown" type against citext column types. I'm guessing that this due to the lack of support for citext type in Pg JDBC driver. Normally, a data type not known by the driver is returned by ResultSet.getObject() as a PGobject instance.  So when we are using a PreparedStatement, during binding of parameters, the PG JDBC driver is unable to provide a correct data type mapping for citext type (which is an unknown type to it)  so it considers it to be a default type of varchar. Since the citext column is now treated as varchar, the search becomes a case-sensitive one and the query returns no rows. The workarounds provided to add "stringtype=unspecified" or creating a custom PGObject type might address this issue but it creates other new issues and it doesn't work with ORM tools. Furthermore, the ORM tool (Hibernate) we use also does not extend support for citext. Only when the driver starts providing support for citext can we approach the Hibernate community with the ORM based issues.

 

 

If not, I would like to provide a patch for the same. I'm not very comfortable making these tweaks by adding custom PGobject data types in our production code and would want to provide a cleaner solution by adding support for "citext" in the PostgreSQL jdbc driver itself. A similar change was done sometime back to extend support for "json" type. We could do the needful and start providing support for citext type by making changes in driver code to identify this new data type introduced on server side. I believe that any new data type or feature added on server side must have relevant client side support that allows us to utilize the feature to the fullest. The case here would be for supporting citext data type on client side.

 

When I checked many forums, I have seen people complaining about these issues but I'm not aware if a fix has been provided or even is in progress. I have gone through the driver code and have made the necessary changes to support this type. I would also want your valuable inputs on what are the other changes that are needed around this area. I'd be more than happy to contribute to the development of this driver and provide a fix to address this issue. Please let me know how to proceed with the issues at hand. Eagerly looking forward to your response.

 

Regards,

Varsha.