Special characters in Hibernate column names

Question

I have a table with a column called @id I want to execute this query :

select DISTINCT "@id" from "osm_art_center"

in Java (Hibernate).

The problem is that Hibrernate could not understand @id and returns an Error that the id column does not exist.

EDIT

Code that causes error:

String columnName="@id";
String tableName="osm_art_center";
ResultSet polygonSearch;
polygonSearch = st.executeQuery("select DISTINCT "+columnName+" from "+tableName);

Error that has been thrown:

SQL Error: org.postgresql.util.PSQLException: ERROR: column "id" does not exist

Answer 1

Try quoting the column name:

String sql ="select DISTINCT \"@id\" from osm_art_center;
ResultSet polygonSearch = st.executeQuery(sql);

The Java language is not stripping or altering the "@" character your string. It's entirely possible, however, that the JDBC driver you're using happens to behave differently than your PostgreSQL command interpreter.

Either way, try escaping the special character.

Answer 2

I what's happening here is that Hibernate isn't quoting identifiers when passed to the database, so PostgreSQL is interpreting the unquoted @id as the operator @ applied to the identifier id. No space is required before a prefix operator; much like you can write a+b instead of a + b, so you can write @id or @ id to apply the operator @ to the column id.

Demonstration:

test=> CREATE TABLE iddemo("@id" integer);
CREATE TABLE
test=> SELECT @id FROM iddemo;
ERROR:  column "id" does not exist
LINE 1: SELECT @id FROM iddemo;

If you quote the identifier it instead produces the expected result:

test=> SELECT "@id" FROM iddemo;
 @id 
-----
(0 rows)

This is IMO a Hibernate bug. It should be quoting all identifiers it passes to the database. Failure to do so means the user has to manually quote identifiers if they want anything upper case, containing spaces, using reserved words, etc. It looks like they've fixed this but not enabled the fix by default for backward compatibility; see Automatic reserved word escaping for Hibernate tables and columns .

You can work around this bug by inserting the quoting in your definitions, though it may have side effects elsewhere.

String columnName="\"@id\"";

or fix it globally in your Hibernate project configuration by setting hibernate.globally_quoted_identifiers to true in your persistence.xml (or Hibernate config file or whatever).