Alex Rivera | Logout

Postgresql CITEXT Datatype Issue JPA Hibernate

Asked 2012-08-20T02:18:22.700
13

I'm having difficulty using the CITEXT datatype in PostgreSQL using JPA and Hibernate. CITEXT is supposed to provide a case insensitive text datatype but when used with JPA/Hibernate it doesn't behave in a case insensitive manner. Has anyone else had this issue or know a way around it? I have seen some mention (but very, very little) about a JDBC issue, but that went back at least a year and wasn't very clear.

I have a 'nickname' column defined as citext in postgres 9.1. I just did a test to see if it could find a row using a named query as such:

create table test(
    nickname citext
)

@NamedQuery(name = "Person.findByNickname", 
            query = "SELECT p 
                     FROM Person p 
                     WHERE p.nickname = :nickname")

Insert a nickname into the DB:

insert into test values('testNick')

Then run this code:

String nickname = "testNick";

Query q = em.createNamedQuery("Person.findByNickname");
q.setParameter("nickname", nickname);
if (q.getResultList().isEmpty()) {
    return (false);
}
return (true);

This returns 'true' (i.e. there is already a 'testNick' in the database).

If I make this assignment

String nickname = "testnick"; //(lower case 'N')

and run it again it returns 'false'.

Since the column is CITEXT, it should return 'true' again. i.e. case insensitive text.

Using JPA and Hibernate. Anyone have any thoughts?

In the meantime I've changed the column back to varchar and created a functional index for lowercase. And I have to create a native query now to search using database functions. Would like to find out if there is a way I can not have to do this to maintain the database abstraction.

Regards.

hibernate postgresql jpa postgresql-9.1
Edit
Report

1 Answer

16

citext provides case-insensitive operators for use within the database, with other citext values.

What's happening

At a guess, your JPA implementation is explicitly specifying the type of the parameter as text when it creates the parameterized statement. citext doesn't define a citext = text operator, so PostgreSQL casts the citext to text and uses the case-sensitive text = text operator. Effectively, comparing citext to text is case sensitive.

Here's what I think is happening. Given the dummy data:

regress=# CREATE EXTENSION citext;
regress=# CREATE TABLE citest ( x citext );
regress=# INSERT INTO citest(x) VALUES ('FRED'), ('FrEd');
regress=# SELECT * FROM citest;
  x   
------
 FRED
 FrEd
(2 rows)

... a comparison of citext to an unknown string literal will be interpreted as citext=citext and be done case-insensitively:

regress=# SELECT * FROM citest WHERE x = 'FRED';
  x   
------
 FRED
 FrEd
(2 rows)

... but a comparison between citext and an explicitly text typed literal will convert the citext argument to text using citext's implicit cast to text, then do a text=text case sensitive comparison:

regress=# SELECT * FROM citest WHERE x = 'FRED'::text;
  x   
------
 FRED
(1 row)

Or rather, what Hibernate is doing will be closer to:

regress=# PREPARE blah(text) AS SELECT * FROM citest WHERE x = $1;
PREPARE
regress=# EXECUTE blah('FRED');
  x   
------
 FRED
(1 row)

where the type is specified as text when binding a parameter, since Hibernate "knows" that Strings are text.

In other words, you need to get Hibernate to, via PgJDBC, explicitly specify

answered 2012-08-20T02:21:02.523

Your Answer