Re: Issue : Citext type not supported by PG JDBC driver.

Hi Varsha,

On 16 February 2017 at 13:38, Varsha Nagarajan <
Varsha(dot)Nagarajan(at)microfocus(dot)com> wrote:

> 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
>
> *Scenario :*
>
> 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.
>
> *Analysis :*
>
> 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.
>
> *Now the real question is : Is an initiative/development already in place
> for this issue?*
>
Not that I am aware of.

>
> 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.
>

Please provide us with a pull request so we can comment on the code.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com