Can I use the number sign (#) in a column name with PostgreSQL and Hibernate?

Hibernate ORM
1

Using Postgres and creating Entities via Spring JPA and Hibernate…
There is a table with a column that has a # in the name and seems to be causing issues when hibernate tries to access the field…

@ Entity
@ Table(name = "SOMETABLE", schema = "ASCHEMA")
public class GroupDivisionPayPeriod implements Serializable {

...
    @ Column(name = "\"PAYFIELD#\"", length = 2)
    public Long payPeriod;

...
}

As you can see i have tried escaping with quotes but that still gives me an error:

 Caused by: org.postgresql.util.PSQLException: ERROR: column sometab0_.payfield# does not exist
        Position: 93
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
      	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:353)
      	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)

I am not sure if this is a Postgres issue or a Hibernate issue?

2

This is a PostgreSQL exception, not a Hibernate one. Most likely, the SOMETABLE database table does not contain the payfield# column.

If you generated the database schema with Hibernate, it could be that the generation has failed too. Check out the database tables and you will find the issue there.

According to the PostgreSQL documentation, you are not allowed to use the # character in any identifier:

SQL identifiers and key words must begin with a letter ( a - z , but also letters with diacritical marks and non-Latin letters) or an underscore ( _ ). Subsequent characters in an identifier or key word can be letters, underscores, digits ( 0 - 9 ), or dollar signs ( $ ). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

3

The issue it turns out is that Hibernate converts the name to lowercase but my CREATE TABLE after escaping the field name to keep the # is keeping it in uppercase… And it seems it’s case sensitive… So I have changed my CREATE TABLE to use lowercase letters. This seems to have worked.