Home Ask Login Register

Developers Planet

Your answer is one click away!

ilovkatie February 2016

Creating date_trunc index via Hibernate 4.3.5 / JPA 2.1

I want to create index like this below:

CREATE INDEX idx_foo_req_date
ON foo
USING btree
(date_trunc('day'::text, req_date));

How/Can I create it via java hibernate annotations?

I have already read about @Index annotation to use it like:

@Table(name = "foo", indexes = { @Index(name = "idx_foo_req_date", columnList = "req_date") })

but it will create smth like this:

CREATE INDEX idx_foo_req_date
ON foo
USING btree


I thought maybe it is pretty flexible and simple and i have tried:

@Table(name = "foo", indexes = { @Index(name = "idx_foo_req_date", columnList = "date_trunc('day'::text, req_date)") })

However org.hibernate.AnnotationException has been thrown, complaining about columns in columnList.


Vlad Mihalcea February 2016

Hibernate uses the JPA @Index annotation which doesn't support function-based indexes.

You'd better use Flyway and keep your schema migration in incremental scripts.

Post Status

Asked in February 2016
Viewed 3,199 times
Voted 5
Answered 1 times


Leave an answer

Quote of the day: live life