Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel