How to map Postgres _INT8 to Java entity using Hibernate?

Question

There is a table created with an SQL DDL script which has a column of type _INT8. If I try to map it to long (which is Postgres INT8) it throws at the end of the stack.

Caused by: org.hibernate.HibernateException: Wrong column type in [schme_name].[table_name] for column [column_name]. Found: _int8, expected: int8
    at org.hibernate.mapping.Table.validateColumns(Table.java:373)
    at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1265)
    at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:155)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:508)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1750)
    at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:94)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:920)

If I try to map it to long[] (or any other array type) instead of Found: _int8, expected: bytea

How can Postgres' _INT8 be mapped into a Java Type using Hibernate?

Answer 1

_int8 is an internal alias for the type int8[], ie an array of long integers.

I have no idea why the underscore prefix is used, it's horrible, but it should really only be visible inside the server so I'm surprised you're seeing it come up in messages. Take this, for example, where the server shows bigint[] as the column type in messages:

http://sqlfiddle.com/#!12/61bc5/1

If you want to map it in Hibernate, you must map it as a long[], if Hibernate even supports SQL arrays - which it does not appear to. You'll probably have to add your own UserType implementation that uses the JDBC support for SQL arrays. Another example on the Hibernate forums. It seems to be a bit of an FAQ, but like most things in Hibernate/JPA you'll find that as soon as you try to use anything but the most basic database features you'll be banging your head against a brick wall.