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

String columnName="@id"; String tableName="osm_art_center"; ResultSet polygonSearch; polygonSearch = st.executeQuery("select DISTINCT "+columnName+" from "+tableName); — Raha Bahredar, Jul 11 '15 at 21:27
that is my code and the Error is this: SQL Error: org.postgresql.util.PSQLException: ERROR: column "id" does not exist — Raha Bahredar, Jul 11 '15 at 21:27
I think that the error is returned per postgres and not java. try to execute this query in your SGBD. — Kachna, Jul 11 '15 at 21:42
I execute this query in Postgres!it does work! the problem is, java could not accept @ then Postgres get the query with just id! — Raha Bahredar, Jul 11 '15 at 21:46
Why would you name a column `"@id"` anyway? Sure, it's legal, but it's asking for problems. — Craig Ringer, Jul 11 '15 at 23:43
Also, tip: `@` is Unicode, but only in the same way that `a` is Unicode. It's part of the 7-bit subset of ASCII that's the most basic character set on pretty much all systems. — Craig Ringer, Jul 11 '15 at 23:59
The code you have posted does not seem to be Hibernate code. You are calling `executeQuery` on a variable named `st` and getting a `ResultSet` back. This seems like raw JDBC code. How do you think Hibernate is involved in this? Your query is also `select DISTINCT @id from osm_art_center`, not `select DISTINCT "@id" from "osm_art_center"` as you want it to be. If you want to run your own SQL over a raw JDBC connection, you will need to quote identifiers yourself. See @paulsm4's answer below. — manish, Jul 12 '15 at 16:00
@manish - it looks like Craig Ringer believes the OP used Hibernate. Craig Ringer changed the tag to "Hibernate" and edited the post to say "Hibernate". I don't know why he did this :( — paulsm4, Jul 12 '15 at 17:32
I see, my bad, I did not check the edit history. Thanks for pointing it out. — manish, Jul 12 '15 at 17:56

score 3 · Answer 1 · answered Jul 11 '15 at 22:08

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.

score 3 · Answer 2 · edited May 23 '17 at 11:43

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).

Special characters in Hibernate column names

2 Answers2